<?php 
/**
 * Project: SCS time
 * funtion.php: lib
 * Author: scs team
 * Create date: 2008/04/08
 * @Copyright 2008 by Viet Nhat Corp. All Rights Reserved.
	
	*/

 //$dao = new DAO();
	include("class.phpmailer.php");
	function getDateFormat($format,$date){
		if(trim($date)=="")
			return "";
		list($year,$month,$day,$hour,$minute,$second) = split('[/.:-]',$date);
		
		$dateFormated = date($format,mktime($hour,$minute,$second,$month,$day,$year));
		return $dateFormated;
	}

	/*
	 * function name: UploadFile
 	 * Author: scs team
	 * @Input: mysqlConnection $con, varchar $area
	 * @Return: array $arr
	 * return array salon_detail_id 
	*/
	
function check_count_review_timesheet_updated($id_user,$date_selected,$is_working_date,$last_date_working,$last_working_date_before_yesterday)
{
	$count = 0;
	$time_current = gmdate("His",time() + 7*3600);
	$date_regis = "";
	if($time_current < TIME_CHECK_TIMESHEET || $is_working_date == false) {
		$date_regis = $last_working_date_before_yesterday;
	}
	else {
		$date_regis = $last_date_working;
	}
	//lay danh sach cac contract ma nguoi do lam leader
	$sql_contract_leader = "
			SELECT contract.contract_id, contract.scs_contract_per1
			FROM contract, contract_type 
			WHERE contract.scs_contract_per1 = ".$id_user." 
				AND contract.delete_status='n' 
				AND contract_type.job_type = 'Monthly'
				AND contract.contract_type_id = contract_type.contract_type_id
				AND contract.end_date >= '$date_regis'
		";
	$query_contract_leader = mysql_query($sql_contract_leader);
	$arr_contract_leader = array();
	if(mysql_num_rows($query_contract_leader) > 0) {
		while($row_contract_leader = mysql_fetch_array($query_contract_leader, MYSQL_BOTH)){
			$arr_contract_leader[$row_contract_leader['contract_id']] = $row_contract_leader;
		}
	}
	$sql_get_timesheets_of_contract_leader = "
		SELECT dwdt.Contact_id, c.con_id, c.scs_contract_per1
		FROM daily_working_data_temp dwdt left join contract c
				ON (dwdt.Contact_id = c.contract_id)
		WHERE dwdt.delete_status = 'n'
			AND dwdt.is_examined = 'pending'
			AND dwdt.date_regis <= '$date_regis'		
	";
	$rst_ts_contract_leader = mysql_query($sql_get_timesheets_of_contract_leader);
	if(mysql_num_rows($rst_ts_contract_leader) > 0) {
		while($row_ts_contract_leader = mysql_fetch_array($rst_ts_contract_leader)){
			if($row_ts_contract_leader['scs_contract_per1'] == $id_user) {
				++$count;
			} else if($row_ts_contract_leader['con_id'] != '0' &&
				$arr_contract_leader[$row_ts_contract_leader['con_id']]['scs_contract_per1'] == $id_user) {
				++$count;
			}
		}
	}
	//lay danh sach cac internal task ma nguoi do lam leader
	$get_iw_leader = "
		SELECT iw_id, branch_id
		FROM internal_work_manger
		WHERE delete_status = 'n'
			AND id_leader = '$id_user'
	";	
	$rst_iw_leader = mysql_query($get_iw_leader);
	$get_ts_iw_leader = "";
	if(mysql_num_rows($rst_iw_leader) > 0) {
		$temp = "";
		while($row_iw_leader = mysql_fetch_assoc($rst_iw_leader)) {
			if($temp != "") {
				$get_ts_iw_leader .= " OR ";	
			}
			$get_ts_iw_leader .= "
				( iw_id = '".$row_iw_leader['iw_id']."' AND  branch_id = '".$row_iw_leader['branch_id']."' )
			";	
			$temp = $row_iw_leader['iw_id'];
		}
		$sql_get_timesheets_of_contract_leader = "
			SELECT count(*) as num
			FROM daily_working_data_iw_temp 
			WHERE delete_status = 'n'
				AND is_examined = 'pending'
				AND date_regis <= '$date_regis'
				AND (".$get_ts_iw_leader.")		
		";
		$rst_ts_iw_leader = mysql_query($sql_get_timesheets_of_contract_leader);
		if(mysql_num_rows($rst_ts_iw_leader) > 0) {
			$row_ts_iw_leader = mysql_fetch_array($rst_ts_iw_leader);
			$count += $row_ts_iw_leader['num'];
		}
	}
	
	return $count;
}
	
	
	function UploadFile($tmp_file,$uploadfile){
		
		//$uploaddir = "../details/img/";  
		$uploadfile = "../../../details/img/".$uploadfile;			
		
		if (move_uploaded_file($tmp_file, $uploadfile)) {			
			//echo "File is valid, and was successfully uploaded.\n";
			//header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
			//header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
			
		} else {
			echo "pls check your url\n";
			//print_r($_FILES);
		}		
	}
	
	/*
	 * function name: SendMail
	 * Author: scs team
	 * @Input: mysqlConnection $con, varchar $area
	 * @Return: array $arr
	 * return array salon_detail_id 
	*/
	
	function sendMail($to,$subject,$body,$cc=""){	
		//convert , va ; thanh ;
        $to = str_replace(',', ';', $to);
        $cc = str_replace(',', ';', $cc);
        
        $arr_to=explode(";",trim($to));
		$arr_cc=explode(";",trim($cc));
		//$arr_cc = split(",|;",trim($cc));
		$mail             = new PHPMailer();
		$mail->IsSMTP();
		//Luyenlv
		//$mail->Mailer = "mail";
		//End Luyenlv
		$mail->SMTPAuth   = true;                  // enable SMTP authentication
		//$mail->SMTPSecure = "ssl";                 // sets the prefix to the servier
		$mail->Host       = "mekongtec.sakura.ne.jp";      // sets GMAIL as the SMTP server
		$mail->Port       = 25;                   // set the SMTP port for the GMAIL server
		$mail->Username   = "scs-host@vinicorp.com.vn";    // GMAIL username
		$mail->Password   = "vinicorpscs";              // GMAIL password
		          
		$mail->AddReplyTo("no-reply@trnavi.net ","TRnavi");
		$mail->From       = "no-reply@trnavi.net ";
		$mail->FromName   = "TRnavi";	
		$mail->CharSet ="utf-8";		
		
		
		//Adress  to
		for($i=0;$i<count($arr_to);$i++)
		 {
		
			if(trim($arr_to[$i])!="")
			{
				$mail->AddAddress(trim($arr_to[$i]));

			}
		}
		
		if(count($arr_cc)>0)
		{
			for($j=0;$j<count($arr_cc);$j++)
			{
				
				if(trim($arr_cc[$j])!="")
				{
					$mail->AddCC(trim($arr_cc[$j]));
				}
			}	
		}	

		//Subject
		$mail->Subject=$subject;
		//Mesage
		$mail->MsgHTML($body);
		
		//Send mail		
		 if(!$mail->Send()) 
		 {			
		  	 echo "Mailer Error : " . $mail->ErrorInfo;
		  	 return false;
		 } else {
		  	 return true;
		 } 	
	}
	

	/*
	 * function name: editURL
 	 * Author: scs team
	 * @Input: $url
	 * @Return: array $url
	 * return array salon_detail_id 
	*/
	function editURL($url){
		$tmp = strpos($url,"http://");
		if($tmp===false)
			$url = "http://".$url;
		return $url;
	}

	/*
	 * function name: checkLogin
	 * Author: scs team
	 * @Input: mysqlConnection $con, varchar $salon_id
	 * @Return: array $arr
	 * return array salon_detail_id 
	*/
	function checkLogin()
	{
		
		if(!isset($_SESSION['check_login']))			
			header("Location:".PROJECT_URL."/ec/login/index.php");
	}

	/*
	 * function name: removeBackSpace
	 * Author: scs team
	 * @Input: mysqlConnection $con, int $category
	 * @Return: boolean
	 * return email content
	*/
	function removeBackSpace($str){
		return str_replace("&nbsp;"," ",$str);
	}
	
	
	/*
	 * function name: testac
	 * Author: scs team
	 * @Input: rank_id, function_name
	 * Return: boolean
	*/
	function testac($rank_id, $function_name)
	{
	
		$sql="select * from access_control inner join function_sys on(access_control.id_function=function_sys.id_function) where ((access_control.rank_id='".$rank_id."') and  (function_name='".$function_name."'))";
		$result=mysql_query($sql);
		//$row=mysql_fetch_array(mysql_query($sql));
		if((mysql_num_rows($result)>0)||($_COOKIE["sys_type_of_user"]=="Administrator"))
		{
			return true;
		 }else
			return false;
	}
	
	//Hoang Manh Kiem - start
	/*******workflow*****************/
	function check_display_choose_wf($module_id){
		$sql = "
            SELECT distinct wf_id, wf_name
    		FROM workflow_modules
    		INNER JOIN workflows ON workflow_modules.wm_wf_id = workflows.wf_id
    		INNER JOIN workflow_valid_dates ON workflows.wf_id = workflow_valid_dates.wvd_wf_id
    		WHERE wm_mod_id ='".$module_id."'
    		AND wm_delete_status = 'n'
    		AND now() >=workflow_valid_dates.wvd_valid_date
    		AND workflows.wf_delete_status = 'n'
    		order by wf_id";
			$query = mysql_query($sql);
			if(mysql_num_rows($query)>0){
				return mysql_num_rows($query);
			}else{
    		  return 0;
            }
	}
	
	function check_delete_wf($wf_id, $user_rank){
		if (testac($user_rank,'Delete_workflow')==false){
			return false;
		}else{
			$sql = "select * from workflow_modules where wm_wf_id = '".$wf_id."' and wm_delete_status = 'n'";
			$query = mysql_query($sql);
			if(mysql_num_rows($query)>0){
				return false;
			}
		}
		return true;
	}
	
	function check_edit_wf($wf_id, $user_rank){
		if (testac($user_rank,'Edit_workflow')==false){
			return false;
		}else{
			return true; //sua dc binh thuong
		}
	}
	
	function get_string_steps($wvd){
		$sql = "SELECT full_name, g_name, wfs_control_status, wfs_deadline
				FROM workflow_steps ws
				LEFT JOIN tbl_users us ON (ws.wfs_user_id = us.id_user)
				LEFT JOIN groups gr ON (ws.wfs_g_id = gr.g_id)
				WHERE wfs_wvd_id = '".$wvd."'
				AND wfs_delete_status = 'n'
				ORDER BY wfs_step 
				";
		$query = mysql_query($sql);
		$count = 1;
		$str = "";
		while($row = mysql_fetch_array($query)){
			$str .= "Step ".$count.": ";
			if($row['wfs_control_status']==1){
				$str .= "<font color=#FF0000 title=\"Step control\">";
			}
			if($row['g_name']==""){
				$str .= $row['full_name'];
			}else{
				$str .= $row['g_name'];
			}
			if($row['wfs_control_status']==1){
				$str .= "</font>";
			}
			if($row['wfs_deadline']!=0){
				$str .= " - ".$row['wfs_deadline']." day(s)";
			}
			$str .= "<br>";
			$count++;
		}
		return $str;
	}
	function count_wf_valid_date($wf_id){
		$sql = "select * from workflow_valid_dates where wvd_wf_id = '".$wf_id."'";
		$query = mysql_query($sql);
		return mysql_num_rows($query);
	}
	/**********************************/
	function check_delete_rank ($rank_id, $function_name, $rank_id_check)
	{
		if(testac($rank_id, $function_name)==false)
		{
			return true;
		}else
		{
			$sql_check = "select * from tbl_users where rank_id = '".$rank_id_check."' and delete_status = 'n'";
			$result_check=mysql_query($sql_check);
			//$row=mysql_fetch_array(result_check);
			if((mysql_num_rows($result_check)>0))
			{
				return true;
			 }else
				return false;
		}
	}
	
	function check_delete_division ($rank_id, $function_name, $division_id_check)
	{
		if(testac($rank_id, $function_name)==false)
		{
			return true;
		}else
		{
			$sql_check = "select * from division_time where division_id = '".$division_id_check."' and delete_status = 'n'";
			$result_check=mysql_query($sql_check);
			//$row=mysql_fetch_array(result_check);
			if((mysql_num_rows($result_check)>0))
			{
				return true;
			 }else
				return false;
		}
	}
	/** ************
		*/
		function check_delete_group ($group_id, $function_name, $group_id_check)
	{
		if(testac($group_id, $function_name)==false)
		{
			return true;
		}else
		{
			$sql_check = "select * from workflow_steps where wfs_g_id = '".$group_id_check."' and wfs_delete_status = 'n'";
			$result_check=mysql_query($sql_check);
			//$row=mysql_fetch_array(result_check);
			if((mysql_num_rows($result_check)>0))
			{
				return true;
			 }else
				return false;
		}
	}
	/** ************
		*/
	function check_delete_branch ($rank_id, $function_name, $branch_id_check)
	{
		if(testac($rank_id, $function_name)==false)
		{
			return true;
		}else
		{
			$sql_check_user = "select * from tbl_users where branch_id = '".$branch_id_check."' and delete_status = 'n'";
			$result_check_user=mysql_query($sql_check_user);
			if((mysql_num_rows($result_check_user)>0))
			{
				return true;
			 }else
			 {
				$sql_check_client = "select * from client_list where branch_id = '".$branch_id_check."' and delete_status = 'n'";
				$result_check_client=mysql_query($sql_check_client);
				if((mysql_num_rows($result_check_client)>0))
				{
					return true;
				}else
				{
					$sql_check_contract = "select * from contract where branch_id = '".$branch_id_check."' and delete_status = 'n'";
					$result_check_contract=mysql_query($sql_check_contract);
					if((mysql_num_rows($result_check_contract)>0))
					{
						return true;
					}else
					{
						return false;
					}
				}
			 }
		}
	}
	
	function check_delete_industrial ($rank_id, $function_name, $industrial_id_check)
	{
		if(testac($rank_id, $function_name)==false)
		{
			return true;
		}else
		{
			$sql_check = "select * from client_list where Industrial_park_ID = '".$industrial_id_check."' and delete_status = 'n'";
			$result_check=mysql_query($sql_check);
			//$row=mysql_fetch_array(result_check);
			if((mysql_num_rows($result_check)>0))
			{
				return true;
			 }else
				return false;
		}
	}
	//Hoang Manh Kiem - end
	
	
function getSwt($date)
{
	$sql="Select wc_swt from tbl_working_calender where (wc_date='".$date."') ";
	$result=mysql_query($sql) or die(err_msg());
	$row=mysql_fetch_array(mysql_query($sql));
	if(mysql_num_rows($result)>0)
	{
		return $row["wc_swt"];
	}
}

  //input : $local_hour, local hour of server  
  //output : Vietnam localize server hour
 function getVnHour($local_hour) {
  $gmt_string = date("O"); 
  $add_bool = false;
  if (substr($gmt_string, 0, 1) == "+") {
   $add_bool = true;
  } else {   
   $add_bool = false;
  }
  $hour_val = substr($gmt_string, 1 , 2);  
  if ($add_bool == false) {
   $hour_val = -intval($hour_val);
  }
  $VN_hour = (intval($local_hour) - $hour_val) + 7;
  return $VN_hour;
 }
 
function get_yesterday_date($str_date) 
{
  list($year, $month, $day) = split("-", $str_date);
  return date('Y-m-d', mktime(0, 0, 0, $month , $day - 1, $year));
}
 
function get_tomorrow_date($str_date) 
{
  list($year, $month, $day) = split("-", $str_date);
  return date('Y-m-d', mktime(0, 0, 0, $month , $day + 1, $year));
}
  
function get_next_date_contract($str_date) 
{
  list($year, $month, $day) = split("-", $str_date);
  $query = mysql_query("Select * from tbl_working_calender where wc_date > '".$str_date."' ORDER BY wc_date");
  //echo "Select * from tbl_working_calender where wc_date > '".$str_date."' ORDER BY wc_date";
  while($row = mysql_fetch_array($query, MYSQL_BOTH))
  {
  	if($row['wc_swt']>0)
	{
		return $row['wc_date'];
	}	
  }
  /*if(date('D', mktime(0, 0, 0, $month , $day, $year))=='Fri')
  {
  	return date('Y-m-d', mktime(0, 0, 0, $month , $day + 3, $year));
  }
  if(date('D', mktime(0, 0, 0, $month , $day, $year))=='Sat')
  {
  	return date('Y-m-d', mktime(0, 0, 0, $month , $day + 2, $year));
  }*/
  return date('Y-m-d', mktime(0, 0, 0, $month , $day + 1, $year));
}
  
function getbeforeday($day)
{
	$beforeday=get_yesterday_date($day);
	if(getSwt($beforeday)==0)
	{
		$beforeday=getbeforeday($beforeday);
	}
	return $beforeday;
} 

 

function charge_rate_current($user_id)
{
	$sqlchar="select * from tbl_users where(delete_status='n')and(id_user='".$user_id."')";
	$resultchar=mysql_query($sqlchar) or die(err_msg());
	$rowchar=mysql_fetch_array($resultchar);
	return $rowchar["charge_rate"];
} 
function getTimeFromDailydata($contract_id)
{
	$sqlGetTime="select sum(working_time) as time_registed from daily_working_data where(delete_status='n')and(Contact_id='".$contract_id."') group by Contact_id";
	$resultGetTime=mysql_query($sqlGetTime);
	$rowGetTime=mysql_fetch_array($resultGetTime);
	$timesheet_con=$rowGetTime["time_registed"];
    $sql = " SELECT contract_id from contract where delete_status='n' and con_id='".$contract_id."' ";
    $qr = mysql_query($sql);
    $string = "0";
    if(mysql_num_rows($qr)>0){
        while($row = mysql_fetch_array($qr)){
            $string .=','.$row['contract_id'];
        }
    }
	$sqlGetTime_pro="select sum(working_time) as time_registed from daily_working_data where Contact_id in (".$string.") and delete_status='n'";
	$resultGetTime_pro=mysql_query($sqlGetTime_pro);
	$rowGetTime_pro=mysql_fetch_array($resultGetTime_pro);	
	$timesheet_pro=$rowGetTime_pro["time_registed"];
	
	$timesheet_total=$timesheet_con+$timesheet_pro;
	return $timesheet_total;
} 
//get timecharge SCS41
/***********************************
Ham lay timecharge
***********************************/
function get_timecharge($client_id){
	$timecharge = 0;
	$date = get_timecharge_date($client_id);
	$sql_timecharge = "Select SUM(working_time*charge_rate_current) as total from daily_working_data_iw where client_id='".$client_id."'
		and date_regis > '".$date."' and delete_status='n'";
	$query_timecharge = mysql_query($sql_timecharge);
	$row_timecharge = mysql_fetch_array($query_timecharge, MYSQL_BOTH);
	$timecharge += $row_timecharge['total'];
	return $timecharge;
}
///lay thoi gian
function get_timecharge_date($client_id){
	//lay thoi gian xa nhat cua PS dang mo
	$sql_open = "Select * from quotation_for_service
					where qfs_client_id = '".$client_id."' and qfs_closing_date = '0000-00-00' and qfs_starting_date <= '".date('Y-m-d')."' and qfs_delete_status='n' order by qfs_starting_date asc";
	$query_open = mysql_query($sql_open);
	if(mysql_num_rows($query_open)<=0){
		//khong co PS nao dang mo, lay ngay ket thuc gan day nhat
		$sql_get_date = "select * from quotation_for_service
							where qfs_client_id = '".$client_id."' and qfs_delete_status='n' order by qfs_closing_date desc";
		$query_get_date = mysql_query($sql_get_date);
		if(mysql_num_rows($query_get_date)<=0){
			$date = '0000-00-00';
			return $date;
		}else{
			$row_get_date = mysql_fetch_array($query_get_date, MYSQL_BOTH);
			if( $row_get_date['qfs_closing_date'] < date('Y-m-d')){
				return $row_get_date['qfs_closing_date'];
			}
			$date_compare_close = $row_get_date['qfs_starting_date'];
		}
		while(true){
			$sql_close = "Select * from quotation_for_service
						where qfs_client_id = '".$client_id."' and qfs_closing_date >= '".$date_compare_close."' and qfs_starting_date < '".$date_compare_close."' and qfs_delete_status='n' order by qfs_starting_date asc";
			$query_close = mysql_query($sql_close);
			if(mysql_num_rows($query_close) <= 0){
				//truong hop khong ton tai cac PS giao nhau, lay PS ket thuc gan nhat voi thoi gian so sanh
				$sql_get_date = "select * from quotation_for_service
									where qfs_client_id = '".$client_id."' and qfs_closing_date < '".$date_compare_close."' and qfs_delete_status='n' order by qfs_closing_date desc";
				$query_get_date = mysql_query($sql_get_date);
				if(mysql_num_rows($query_get_date)<=0){
					//khong co PS nao ket thuc trong trong thoi gian chon
					$date = '0000-00-00';
					return $date;
				}else{
					//truong hop tim thay PS ket thuc, lay PS ket thuc gan nhat
					$row_get_date = mysql_fetch_array($query_get_date, MYSQL_BOTH);
					$date = $row_get_date['qfs_closing_date'];
					return $date;
				}
			}else{
				//truong hop co PS giao nhau, < truong hop phuc tap nhat > 
				$row_close = mysql_fetch_array($query_close, MYSQL_BOTH);
				$date_compare_close = $row_close['qfs_starting_date'];
			}
		}
	}else{
		//truong hop co PS dang mo, lay ngay bat dau xa nhat
		$row_open = mysql_fetch_array($query_open, MYSQL_BOTH);
		$date_compare = $row_open['qfs_starting_date'];
	}	
	//lay nhung PS co thoi gian ket thuc noi voi PS dang mo
	$sql_close_1 = "Select * from quotation_for_service
						where qfs_client_id = '".$client_id."' and qfs_closing_date >= '".$date_compare."' and qfs_delete_status='n' order by qfs_starting_date asc";
	$query_close_1 = mysql_query($sql_close_1);
	if(mysql_num_rows($query_close_1) <= 0){
		//truong hop khong ton tai cac PS giao nhau, lay PS ket thuc gan nhat voi thoi gian so sanh
		$sql_get_date = "select * from quotation_for_service
							where qfs_client_id = '".$client_id."' and qfs_closing_date < '".$date_compare."' and qfs_delete_status='n' order by qfs_closing_date desc";
		$query_get_date = mysql_query($sql_get_date);
		if(mysql_num_rows($query_get_date)<=0){
			//khong co PS nao ket thuc trong trong thoi gian chon
			$date = '0000-00-00';
			return $date;
		}else{
			//truong hop tim thay PS ket thuc, lay PS ket thuc gan nhat
			$row_get_date = mysql_fetch_array($query_get_date, MYSQL_BOTH);
			$date = $row_get_date['qfs_closing_date'];
			return $date;
		}
	}else{
		//truong hop co PS giao nhau, < truong hop phuc tap nhat > 
		$row_close_1 = mysql_fetch_array($query_close_1, MYSQL_BOTH);
		$date_compare = $row_close_1['qfs_starting_date'];	
	}
	while(true){
		$sql_close_2 = "Select * from quotation_for_service
						where qfs_client_id = '".$client_id."' and qfs_closing_date >= '".$date_compare."' and qfs_delete_status='n' 
						and qfs_starting_date < '".$date_compare."' order by qfs_starting_date asc";
		$query_close_2 = mysql_query($sql_close_2);
		if(mysql_num_rows($query_close_2)<=0){
			//truong hop khong ton tai cac PS giao nhau, lay PS ket thuc gan nhat voi thoi gian so sanh
			$sql_get_date = "select * from quotation_for_service
								where qfs_client_id = '".$client_id."' and qfs_closing_date < '".$date_compare."' 
								and qfs_closing_date <> '0000-00-00'
								and qfs_delete_status='n' order by qfs_closing_date desc";
			$query_get_date = mysql_query($sql_get_date);
			if(mysql_num_rows($query_get_date)<=0){
				//khong co PS nao ket thuc trong trong thoi gian chon
				$date = '0000-00-00';
				return $date;
			}else{
				//truong hop tim thay PS ket thuc, lay PS ket thuc gan nhat
				$row_get_date = mysql_fetch_array($query_get_date, MYSQL_BOTH);
				$date = $row_get_date['qfs_closing_date'];
				return $date;
			}
		}else{
			$row_close_2 = mysql_fetch_array($query_close_2, MYSQL_BOTH);
			$date_compare = $row_close_2['qfs_starting_date'];
		}
	}
	return '0000-00-00';
}

//lay thoi gian da nhap timesheet cua khach hang chua co PS:
function check_time_client_no_ps($client_id, $branch_id, $date, $user, $dwd_update){
	$count = 0;
	//start
	$start_date_check = "SELECT qfs.qfs_closing_date
											FROM client_list cl, quotation_for_service qfs 
											WHERE cl.Client_id = '".$client_id."'
											AND cl.branch_id = '".$branch_id."'
											AND cl.delete_status='n'
											AND cl.Client_id = qfs.qfs_client_id
											AND qfs.qfs_delete_status = 'n'
											AND qfs.app_status='approval'
											AND qfs.app_status_edit = 'approval'
											AND cl.app_status='approval'
											AND qfs.qfs_closing_date < '".$date."'
											ORDER BY qfs_closing_date DESC limit 0,1";
	$query_start_date_check = mysql_query($start_date_check);
	if(mysql_num_rows($query_start_date_check)>0){
		$row_start_date_check = mysql_fetch_array($query_start_date_check);
		$start = "and date_regis > '".$row_start_date_check['qfs_closing_date']."'";
	}else{
		$start = '';
	}
	//end
	$end_date_check = "SELECT qfs.qfs_starting_date
											FROM client_list cl, quotation_for_service qfs 
											WHERE cl.Client_id = '".$client_id."'
											AND cl.branch_id = '".$branch_id."'
											AND cl.delete_status='n'
											AND cl.Client_id = qfs.qfs_client_id
											AND qfs.qfs_delete_status = 'n'
											AND qfs.app_status='approval'
											AND qfs.app_status_edit = 'approval'
											AND cl.app_status='approval'
											AND qfs.qfs_starting_date > '".$date."'
											ORDER BY qfs_starting_date limit 0,1";
	$query_end_date_check = mysql_query($end_date_check);
	if(mysql_num_rows($query_end_date_check)>0){
		$row_end_date_check = mysql_fetch_array($query_end_date_check);
		$end = "and date_regis < '".$row_end_date_check['qfs_starting_date']."'";
	}else{
		$end = '';
	}
	$check_total_timesheet_app = mysql_query("SELECT sum(working_time) as total from daily_working_data_iw where user_id = '".$user."' ".$start." ".$end." and delete_status='n' and client_id = '".$client_id."' and dwd_iw_id <> '".$dwd_update."'");
	$row_check_total_timesheet_app = mysql_fetch_array($check_total_timesheet_app, MYSQL_BOTH);
	$count += $row_check_total_timesheet_app['total'];
	$check_total_timesheet_pend = mysql_query("SELECT sum(working_time) as total from daily_working_data_iw_temp where user_id = '".$user."' ".$start." ".$end." and delete_status='n' and client_id = '".$client_id."' and dwd_iw_id <> '".$dwd_update."'");
	$row_check_total_timesheet_pend = mysql_fetch_array($check_total_timesheet_pend, MYSQL_BOTH);
	$count += $row_check_total_timesheet_pend['total'];
	return $count;
}
function check_date_working($date) {
	$sql_select = "select wc_swt from tbl_working_calender where wc_date = '".$date."' order by wc_date desc";
	$resultGetTime=mysql_query($sql_select);
	$rowGetTime=mysql_fetch_array($resultGetTime);
	if($rowGetTime["wc_swt"] > 0) {
		return true;
	}else{
		return false;
	}
}
function check_last_date_working_after_yesterday_timesheet($date)
{
	$sql_select = "select wc_date from tbl_working_calender where wc_date < '".check_last_date_working_timesheet($date)."' and wc_swt > 0 order by wc_date desc";
	$resultGetTime=mysql_query($sql_select);
	$rowGetTime=mysql_fetch_array($resultGetTime);
	return $rowGetTime["wc_date"];
}
function check_last_date_working_timesheet($date)
{
	$sql_select = "select wc_date from tbl_working_calender where wc_date < '".$date."' and wc_swt > 0 order by wc_date desc";
	$resultGetTime=mysql_query($sql_select);
	$rowGetTime=mysql_fetch_array($resultGetTime);
	return $rowGetTime["wc_date"];
}
//lay ngay lam viec hop le truoc ngay hien tai
function check_last_date_working()
{
	$sql_select = "select wc_date from tbl_working_calender where wc_date < '".gmdate("Y-m-d",time() + 7*3600)."' and wc_swt > 0 order by wc_date desc";
	$resultGetTime=mysql_query($sql_select);
	$rowGetTime=mysql_fetch_array($resultGetTime);
	return $rowGetTime["wc_date"];
}

function check_last_date_working_after_yesterday()
{
	$sql_select = "select * from tbl_working_calender where wc_date < '".check_last_date_working()."' and wc_swt > 0 order by wc_date desc";
	$resultGetTime=mysql_query($sql_select);
	$rowGetTime=mysql_fetch_array($resultGetTime);
	return $rowGetTime["wc_date"];
}

//Check ngay nghi
function check_date_is_not_working($date)
{
	$sql1 = "SELECT * FROM tbl_working_calender WHERE wc_date = '".$date ."'";
	$query1 = mysql_query($sql1);
	$num_rows1 = mysql_num_rows($query1);
	if($num_rows1==0)
	{
		return true;
	} else {
		$sql2 = "SELECT * FROM tbl_working_calender WHERE wc_swt = 0 AND wc_date = '".$date ."'";
		$query2 = mysql_query ($sql2);
		$num_rows2 = mysql_num_rows($query2);
		if($num_rows2==0)
		{
			return false;
		} else{
			return true;
		}
	}
}


//tra ve ngay can kiem tra xem cac timesheet lien quan da dc approve/examine/confirm chua trong mot so module lien quan 
function get_date_to_check(){
	$today = gmdate("Y-m-d",time() + 7*3600);
	$time_current = gmdate("His",time() + 7*3600);
	$sql_today = "select * from tbl_working_calender where wc_date = '".$today ."'";
	$query_today = mysql_query ($sql_today);
	$row_today = mysql_fetch_array($query_today);
	if ($row_today['wc_swt'] == 0 || mysql_num_rows($query_today)==0) //la ngay nghi
	{
		$sql_select = "select * from tbl_working_calender where wc_date < '".$today."' and wc_swt > 0 order by wc_date desc limit 1,1";
	}
	else {
		//if ($time_current <= "080000"){ //truoc 8h cua ngay lam viec, kiem tra ngay lam viec ke truoc nua
		if ($time_current <= TIME_CHECK_TIMESHEET){ //truoc 8h cua ngay lam viec, kiem tra ngay lam viec ke truoc nua
			$sql_select = "select * from tbl_working_calender where wc_date < '".$today."' and wc_swt > 0 order by wc_date desc limit 1,1";
		}
		else {
			$sql_select = "select * from tbl_working_calender where wc_date < '".$today."' and wc_swt > 0 order by wc_date desc limit 0,1";
		}
		
	}
	$query_select = mysql_query($sql_select);
	$row_select = mysql_fetch_array($query_select);
	return $row_select['wc_date'];
}
function get_date_working(){
	$today = gmdate("Y-m-d",time() + 7*3600);
	$time_current = gmdate("His",time() + 7*3600);
	$sql_today = "select * from tbl_working_calender where wc_date = '".$today ."'";
	$query_today = mysql_query ($sql_today);
	$row_today = mysql_fetch_array($query_today);
	if ($row_today['wc_swt'] == 0 || mysql_num_rows($query_today)==0) //la ngay nghi
	{
		$sql_select = "select * from tbl_working_calender where wc_date <= '".$today."' and wc_swt > 0 order by wc_date desc limit 0,1";
	}
	else {
		//if ($time_current <= "080000"){ //truoc 8h cua ngay lam viec, kiem tra ngay lam viec ke truoc nua
		if ($time_current <= TIME_CHECK_TIMESHEET){ //truoc 8h cua ngay lam viec, kiem tra ngay lam viec ke truoc nua
			$sql_select = "select * from tbl_working_calender where wc_date <= '".$today."' and wc_swt > 0 order by wc_date desc limit 1,1";
		}
		else {
			$sql_select = "select * from tbl_working_calender where wc_date <= '".$today."' and wc_swt > 0 order by wc_date desc limit 0,1";
		}
		
	}
	$query_select = mysql_query($sql_select);
	$row_select = mysql_fetch_array($query_select);
    //if (check_date_working($today)&& $time_current > "080000"){
    if (check_date_working($today)&& $time_current > TIME_CHECK_TIMESHEET){
        return $today;
    }
    //if (check_date_is_not_working($today) || (check_date_working($today)&& $time_current <= "080000")){
    if (check_date_is_not_working($today) || (check_date_working($today)&& $time_current <= TIME_CHECK_TIMESHEET)){
        return $row_select['wc_date'];
    }
}
function check_count_review_timesheet_pending($id_user)
{
	$count = 0;
	$time_current = gmdate("His",time() + 7*3600);
	$check_last_date_working = check_last_date_working();
	$check_last_date_working_after_yesterday = check_last_date_working_after_yesterday();
	$Swt = getSwt(gmdate("Y-m-d",time() + 7*3600));
	//lay danh sach cac contract ma nguoi do lam leader
	$sql_contract_leader = "select * from contract, contract_type where contract.scs_contract_per1 = ".$id_user." and contract.delete_status='n' and contract.contract_type_id = contract_type.contract_type_id";
	$query_contract_leader = mysql_query($sql_contract_leader);
	while($row_contract_leader = mysql_fetch_array($query_contract_leader, MYSQL_BOTH)){
		//duyet cac timesheet dang onetime
		if($row_contract_leader['job_type']=='Monthly')
		{
			$sqlGetTime="select count(*) as count from daily_working_data_temp inner join contract on (contract.contract_id=daily_working_data_temp.Contact_id) where(contract.delete_status='n') and (contract.con_id='".$row_contract_leader['contract_id']."') and (daily_working_data_temp.is_examined = 'pending') and daily_working_data_temp.delete_status='n' and (daily_working_data_temp.user_id <> ".$id_user.")";
		}else
		{
			$sqlGetTime="select  count(*) as count from daily_working_data_temp where (delete_status='n') and (Contact_id='".$row_contract_leader['contract_id']."') and (daily_working_data_temp.is_examined = 'pending') and daily_working_data_temp.delete_status='n' and (daily_working_data_temp.user_id <> ".$id_user.")";
		}	
		if($Swt>0)
		{
			//if($time_current <= "080000")
			if($time_current <= TIME_CHECK_TIMESHEET)
			{
				$sqlGetTime .= " and daily_working_data_temp.date_regis < '".$check_last_date_working."' ";	
			}else
			{
				$sqlGetTime .= " and daily_working_data_temp.date_regis <= '".$check_last_date_working."' ";	
			}
		}else
		{
			$sqlGetTime .= " and daily_working_data_temp.date_regis <= '".$check_last_date_working_after_yesterday."' ";
		}
		//echo $sqlGetTime;	
		$resultGetTime=mysql_query($sqlGetTime);
		$rowGetTime=mysql_fetch_array($resultGetTime);
		$timesheet_con=$rowGetTime["count"];
		if(number_format($timesheet_con)>0)
		{
			$count += $timesheet_con;
			return false;
		}		
	}
	//lay danh sach cac internal task ma nguoi do lam leader
	$sql_internal_leader = "select * from internal_work_manger where id_leader = ".$id_user." and delete_status='n'";
	$query_internal_leader = mysql_query($sql_internal_leader);
	while($row_internal_leader = mysql_fetch_array($query_internal_leader, MYSQL_BOTH))
	{
		$sqlGetTimeInternal = "select count(*) as count from daily_working_data_iw_temp where iw_id=".$row_internal_leader['iw_id']." and branch_id=".$row_internal_leader['branch_id']." and delete_status='n' and is_examined = 'pending'";
		if($Swt>0)
		{
			//if($time_current <= "080000")
			if($time_current <= TIME_CHECK_TIMESHEET)
			{
				$sqlGetTimeInternal .= " and date_regis < '".$check_last_date_working."' ";	
			}else
			{
				$sqlGetTimeInternal .= " and date_regis <= '".$check_last_date_working."' ";	
			}
		}else
		{
			$sqlGetTimeInternal .= " and date_regis <= '".$check_last_date_working_after_yesterday."' ";
		}
		$resultGetTimeInternal=mysql_query($sqlGetTimeInternal);
		$rowGetTimeInternal=mysql_fetch_array($resultGetTimeInternal);
		$timesheet_inter=$rowGetTimeInternal["count"];
		if(number_format($timesheet_inter)>0)
		{
			$count += $timesheet_inter;
			return false;
		}	
	}
	return true;
}

//ham check trang thai cac timesheet chua dc duyet qua 7 ngay
function check_status_review_timesheet($id_user)
{
	$array_list = array();
	//lay danh sach cac contract ma nguoi do lam leader
	$sql_contract_leader = "select * from contract, contract_type where contract.scs_contract_per1 = ".$id_user." and contract.delete_status='n' and contract.contract_type_id = contract_type.contract_type_id";
	$query_contract_leader = mysql_query($sql_contract_leader);
	while($row_contract_leader = mysql_fetch_array($query_contract_leader, MYSQL_BOTH)){
		//duyet cac timesheet dang onetime
		if($row_contract_leader['job_type']=='Monthly')
		{
		
			$sqlGetTime="select sum(daily_working_data_temp.working_time) as time_registed from daily_working_data_temp inner join contract on (contract.contract_id=daily_working_data_temp.Contact_id) where(contract.delete_status='n') and (contract.con_id='".$row_contract_leader['contract_id']."') and (daily_working_data_temp.is_examined = 'pending') and daily_working_data_temp.delete_status='n' and (daily_working_data_temp.user_id <> ".$id_user.") and (DATE_SUB(CURDATE(),INTERVAL 7 DAY) >= daily_working_data_temp.date_regis) group by contract.con_id";
		}
		else
		{
		
			$sqlGetTime="select sum(working_time) as time_registed from daily_working_data_temp where (delete_status='n') and (Contact_id='".$row_contract_leader['contract_id']."') and (daily_working_data_temp.is_examined = 'pending') and daily_working_data_temp.delete_status='n' and (daily_working_data_temp.user_id <> ".$id_user.") and (DATE_SUB(CURDATE(),INTERVAL 7 DAY) >= date_regis) group by Contact_id";
			
		}	
			
		$resultGetTime=mysql_query($sqlGetTime);
		$rowGetTime=mysql_fetch_array($resultGetTime);
		$timesheet_con=$rowGetTime["time_registed"];
		
		if(number_format($timesheet_con, 2)>0)
		{
			//echo $sqlGetTime;
			//echo number_format($timesheet_con, 2);
			$array_list[] = $row_contract_leader['contract_id'];
		}		
	}
	return $array_list;
}
//num of timesheet
function check_count_review_timesheet($id_user)
{		
	$count = 0;
	$time_current = gmdate("His",time() + 7*3600);
	$check_last_date_working = check_last_date_working();
	$check_last_date_working_after_yesterday = check_last_date_working_after_yesterday();
	$Swt = getSwt(gmdate("Y-m-d",time() + 7*3600));
	//lay danh sach cac contract ma nguoi do lam leader
	$sql_contract_leader = "select contract_id, job_type  from contract, contract_type where contract.scs_contract_per1 = ".$id_user." and contract.delete_status='n' and contract.contract_type_id = contract_type.contract_type_id";
	$query_contract_leader = mysql_query($sql_contract_leader);
	
	while($row_contract_leader = mysql_fetch_array($query_contract_leader, MYSQL_BOTH)){
		//duyet cac timesheet dang onetime
		if($row_contract_leader['job_type']=='Monthly')
		{
			$sqlGetTime="select count(*) as count from daily_working_data_temp inner join contract on (contract.contract_id=daily_working_data_temp.Contact_id) where(contract.delete_status='n') and (contract.con_id='".$row_contract_leader['contract_id']."') and (daily_working_data_temp.is_examined = 'pending' or daily_working_data_temp.is_examined = 'no') and daily_working_data_temp.delete_status='n' and (daily_working_data_temp.user_id <> ".$id_user.")";
		}else
		{
			$sqlGetTime="select  count(*) as count from daily_working_data_temp where (delete_status='n') and (Contact_id='".$row_contract_leader['contract_id']."') and (daily_working_data_temp.is_examined = 'pending' or daily_working_data_temp.is_examined = 'no') and daily_working_data_temp.delete_status='n' and (daily_working_data_temp.user_id <> ".$id_user.")";
		}
		
		if($Swt>0)
		{
			//if($time_current <= "080000")
			if($time_current <= TIME_CHECK_TIMESHEET)
			{
				$sqlGetTime .= " and daily_working_data_temp.date_regis < '".$check_last_date_working."' ";	
			}else
			{
				$sqlGetTime .= " and daily_working_data_temp.date_regis <= '".$check_last_date_working."' ";	
			}
		} else
		{
			$sqlGetTime .= " and daily_working_data_temp.date_regis <= '".$check_last_date_working_after_yesterday."' ";	
		}
		
		$resultGetTime=mysql_query($sqlGetTime);
		$rowGetTime=mysql_fetch_array($resultGetTime);
		$timesheet_con=$rowGetTime["count"];
		if(number_format($timesheet_con)>0)
		{
			$count += $timesheet_con;
		}		
	}
	//lay danh sach cac internal task ma nguoi do lam leader
	$sql_internal_leader = "select iw_id, branch_id from internal_work_manger where id_leader = ".$id_user." and delete_status='n'";
	$query_internal_leader = mysql_query($sql_internal_leader);
	
	while($row_internal_leader = mysql_fetch_array($query_internal_leader, MYSQL_BOTH))
	{
		$sqlGetTimeInternal = "select count(*) as count from daily_working_data_iw_temp where iw_id=".$row_internal_leader['iw_id']." and branch_id=".$row_internal_leader['branch_id']." and delete_status='n' and is_examined <> 'yes'";
		if($Swt>0)
		{
			//if($time_current <= "080000")
			if($time_current <= TIME_CHECK_TIMESHEET)
			{
				$sqlGetTimeInternal .= " and date_regis < '".$check_last_date_working."' ";	
			}else
			{
				$sqlGetTimeInternal .= " and date_regis <= '".$check_last_date_working."' ";	
			}
		} else
		{
			$sqlGetTimeInternal .= " and date_regis <= '".$check_last_date_working_after_yesterday."' ";
		}	
		$resultGetTimeInternal=mysql_query($sqlGetTimeInternal);
		$rowGetTimeInternal=mysql_fetch_array($resultGetTimeInternal);
		$timesheet_inter=$rowGetTimeInternal["count"];
		if(number_format($timesheet_inter)>0)
		{
			$count += $timesheet_inter;
		}	
	}
	return $count;
}


function check_proposal_next_update($id_user,$date) {
	$count = 0;
	$sql_proposal = "select * from quotation_for_service qfs
					inner join client_list on  qfs_client_id = Client_id
					where  pic1='".$id_user."' and qfs_delete_status='n'
					and qfs_closing_date='0000-00-00'
					and qfs_next_update <='".$date."' 
					and qfs.app_status_edit = 'approval'
					and qfs.app_status ='approval'
                    and (qfs.qfs_status IS NULL or qfs.qfs_status ='')";
						
	$query_proposal = mysql_query ($sql_proposal);
	if (mysql_num_rows($query_proposal) >0 ){
		$count = mysql_num_rows($query_proposal);
	}
	return $count;
}
function check_count_timesheet_rejected($id_user)
{
	$count = 0;
	$sql_timesheet_rejected = "select dwd_id as dwd_id from daily_working_data_temp where user_id = ".$id_user." and is_examined='no' and delete_status='n'";
	$sql_timesheet_rejected .= " union select dwd_iw_id as dwd_id from daily_working_data_iw_temp where user_id = ".$id_user." and is_examined='no' and delete_status='n'";
	$query = mysql_query($sql_timesheet_rejected);
	if(mysql_num_rows($query)>0)
	{
		$count = mysql_num_rows($query);
	}
	return $count;
}
//Hoang manh kiem
function check_rfa_over_deadline($id_user){
 	$time = gmdate("Y-m-d H:i:s", time() + 7*3600);
 	//xac dinh user thuoc nhung group nao
 	$sql_select_group = "SELECT DISTINCT g_id
								FROM groups
								INNER JOIN group_members ON g_id = gm_g_id
								WHERE gm_user_id = '".$id_user."'
								AND gm_delete_status = 'n'
								AND g_delete_status = 'n'";
	$query_select_group = mysql_query($sql_select_group);
	if (mysql_num_rows($query_select_group)>0){
		$arr_select_group= "";
		while ($row_select_group = mysql_fetch_array($query_select_group)){
			$arr_select_group.=$row_select_group['g_id'].",";
		}
	}
	else {
		$arr_select_group= "0,";
	}
	$new_arr_select_group = substr($arr_select_group,0,-1);
 	//tim cac ban ghi trong request_steps lien quan den user nay (la user duyet hoac thuoc group duyet)
 	//nhom theo tung request, trong moi request chi chon ban ghi co version cao nhat
 	$select_max_version = "SELECT rs_r_id,rs_g_id,rs_user_id, max(rs_version) as MAX_VERSION
	 						FROM request_steps
							 WHERE (rs_user_id = '".$id_user."'
							 OR (rs_g_id IN (".$new_arr_select_group.") AND rs_user_id = '0'))
							 AND rs_start <> '0000-00-00 00:00:00'
					AND rs_duration <> '0'
                    AND (
					   rs_status = 'waiting' OR rs_status = 'save'
					)
					AND rs_delete_status = 'n'
					GROUP BY rs_r_id";
	$query_max_version = mysql_query($select_max_version);
	//tim ban ghi co version tuong ung cao nhat ma co trang thai la waiting hoac save ->so thoi han deadline voi thoi diem hien tai
	while ($row_max_version = mysql_fetch_array($query_max_version)){
		$sql = "SELECT *, UNIX_TIMESTAMP(rs_start) as times_as
					FROM request_steps
					WHERE (
					rs_status = 'waiting' OR rs_status = 'save'
					)
					AND rs_r_id='".$row_max_version['rs_r_id']."'
					AND rs_version='".$row_max_version['MAX_VERSION']."'
                    AND rs_g_id='".$row_max_version['rs_g_id']."'
                    AND rs_user_id='".$row_max_version['rs_user_id']."'
                    AND rs_delete_status ='n' ";
		$query = mysql_query($sql);
		while($row = mysql_fetch_assoc($query)){
			$total_add = $row['rs_duration'];
			for($i=0;$i<=$total_add;$i++){
				if(check_date_working(gmdate("Y-m-d", $row['times_as'] + 7*3600 + $i*24*3600)) == false){
					$total_add++;
				}
			}
			$deadline = gmdate("Y-m-d H:i:s", $row['times_as'] + 7*3600 + $total_add*24*3600);
			if($deadline < $time){
				return false;
			}
		}
	}
	return true;
}

function UserIsDisabled($id_user){
	$sql_user = "select * from tbl_users where id_user='".$id_user."'";
	$query_user = mysql_query($sql_user);
	$row_user = mysql_fetch_array($query_user);
	if ($row_user['status']=='Enable'){
		return 0;
	}
	else {
		return 1;
	}
}
//ham kiem tra trang thai cua mot nguoi co thoa man dieu kien dc phep nhap timesheet
function check_approval_input_timesheet($id_user, $date){
	
	$time_current = gmdate("His",time() + 7*3600);
    $today = gmdate("Y-m-d",time() + 7*3600);
	$date_check = check_last_date_working_timesheet($date);
	$sql_timesheet_input = "select dwd_id, working_time from daily_working_data_temp where date_regis = '".$date_check."' and delete_status='n' and user_id = ".$id_user."";
	$sql_timesheet_input .= " union all select dwd_iw_id, working_time from daily_working_data_iw_temp where date_regis = '".$date_check."' and delete_status='n' and user_id = ".$id_user."";
	$sql_timesheet_input .= " union all select dwd_id, working_time from daily_working_data where date_regis = '".$date_check."' and delete_status='n' and user_id = ".$id_user."";
	$sql_timesheet_input .= " union all select dwd_iw_id, working_time from daily_working_data_iw where date_regis = '".$date_check."' and delete_status='n' and user_id = ".$id_user."";
    $time_standart = getSwt($date_check);
    $time_check = 0;
    $query_timesheet_input = mysql_query($sql_timesheet_input);
    while($row_timesheet_input = mysql_fetch_assoc($query_timesheet_input)){
        $time_check += $row_timesheet_input['working_time'];
    }	
	
    //lay thong tin ve cac rule tu bang condition_input_timesheet ra;
 	$sql_rule= "select * from condition_input_timesheet";
 	$result_rule = mysql_query($sql_rule);
 	$row_rule = mysql_fetch_array($result_rule);
 	$cit_billing_date= $row_rule["cit_billing_date"];
	$cit_contract_fee_onetime = $row_rule["cit_contract_fee_onetime"];
	$cit_fee_total_monthly = $row_rule["cit_fee_total_monthly"];
	$cit_close_contract = $row_rule["cit_close_contract"];
	$cit_proposal = $row_rule["cit_proposal"];
	$cit_rfa_check = $row_rule["cit_rfa_check"];
	$cit_expire_after = $row_rule["cit_expire_after"];
	$cit_check_note = $row_rule["cit_check_note"];
	// kiem tra user nay co task nao qua han khong
	$now = gmdate("Y-m-d H:i:s", time() + 7*3600);
	$mysql_expire = "select open_date_plan,open_hour_plan,open_minute_plan
					 from process_task 
					 where pic='$id_user' and cast(concat(open_date_plan,' ',open_hour_plan,':',open_minute_plan,':00') as date) <'$now' 
					 and close_content is null";

	$query_expire = mysql_query($mysql_expire);
	$sql0="select * from company";
	$result0=mysql_query($sql0);
	$row0=mysql_fetch_array($result0);

	if(mysql_num_rows($result0)>0){
		$expire_after = $row0["expire_after"];
	}else{
		$expire_after = '0';
	}
	$time_current = gmdate("His", time() + 7*3600);

	while($row_expire = mysql_fetch_array($query_expire)){
		// neu ngay open_date_plan khong qua han qua expire ngay thi cung khong kiem
		// xac dinh open_date_plan + expire_after la ngay bao nhieu
		// duyet qua tung ngay cua working calendar
		// $cit_expire_after		
		if ($cit_expire_after){
			$i=1;	
			$expire_after_date = date("Y-m-d H:i:s", strtotime($row_expire['open_date_plan']." ".$row_expire['open_hour_plan'].":".$row_expire['open_minute_plan'].":00"));
			while($i<=$expire_after){
				if (!check_date_is_not_working(date("Y-m-d",strtotime($expire_after_date)))){					
					$i++;
				}
				$expire_after_date = date("Y-m-d H:i:s", strtotime("next day", strtotime($expire_after_date)));
			}
			// chua den 8h sang thi xem nhu ngay hom truoc
			if ($time_current <= TIME_CHECK_TIMESHEET){
				$now=check_last_date_working();
			}
		
			if ($expire_after_date
				&& strtotime($now)>strtotime($expire_after_date)){
				return 13;
			}			
		}else{
			return 13;
		}
    }
	// kiem tra xem co note nao cua candidate qua han khong
	
	if ($cit_check_note==1){
		$mysql = "select next_update_date from tbl_candidate where person_in_charge=$id_user";
		$rsCheckNote = mysql_query ($mysql);
		$now = date("Y-m-d");
		
		if ($time_current <= TIME_CHECK_TIMESHEET){
			$now=check_last_date_working();
		}		
		while($row = mysql_fetch_array($rsCheckNote)){			
			if ($row["next_update_date"]=="0000-00-00 00:00:00"
			|| $row["next_update_date"]==null
			){
				continue;
			}
			if (strtotime($now)>strtotime($row["next_update_date"]) ){			
				return 14;
			}
		}
	}
	if($time_check == 0){
	//if($time_check < $time_standart){
		//neu la ngay lam viec dau tien -> duoc nhap; neu khong phai -> khong duoc nhap
		$sql_first = "select start_date from tbl_users where id_user = ".$id_user."";
		$query_first = mysql_query ($sql_first);
		$row_first = mysql_fetch_array ($query_first);
		if($date != $row_first['start_date']) { //ko phai ngay dau tien--> phai nhap ngay lam viec ke truoc
			return 0;
		} 
	}
	if(check_count_timesheet_rejected($id_user) > 0){
		return 1;
	}
	if(check_count_review_timesheet_pending($id_user)==false){
		return 2;
	}
	if ($cit_rfa_check=='1' && check_rfa_over_deadline($id_user)==false){
		return 3;
	}
	if ($cit_proposal=='1' && (check_proposal_next_update($id_user,get_date_working()) > 0)){
		return 5;
	}	
 	if (UserIsDisabled($id_user)){
 		return 6;
 	}
	if ($cit_billing_date=='1' ||$cit_contract_fee_onetime=='1'||$cit_fee_total_monthly=='1' || $cit_close_contract=='1' ){
		//tim cac hop dong ma nguoi nay la leader; roi kiem tra tuong ung cac dieu kien trong rule xem co thoa man hay khong
		$sql_all_contract = 'SELECT * FROM contract WHERE scs_contract_per1="'.$id_user.'" AND delete_status="n" AND contract_status<>"Ended"';
		$result_contract = mysql_query($sql_all_contract);
		if (mysql_num_rows($result_contract)>0){
            $string_7 ="";
            $string_8 ="";
            $string_10 ="";
            $string_9 ="";
			while($row_contract = mysql_fetch_array($result_contract)){
				$sql_contract_type= "select job_type,cs_type from contract_type
										where contract_type_id=".$row_contract['contract_type_id']."
										and delete_status='n'";
					$result_contract_type =mysql_query($sql_contract_type);
					$row_contract_type = mysql_fetch_array($result_contract_type);
				//neu khong cho nhap timesheet neu co contract/project chua close dung ki han
				if ($cit_close_contract=='1'){
					//neu la hop dong monthly, kiem tra ca ngay ket thuc project
                    if($row_contract['end_date']!='0000-00-00'){
                        if ($row_contract['end_date'] < get_date_working()){
                            $string_7 .= "7";
                        }
                    }
					if ($row_contract_type['job_type']=="Monthly" || ($row_contract_type['job_type']=="cs" && $row_contract_type['cs_type']=='retainer')){
						$sql_close_project = "select * from contract
											where con_id='".$row_contract['contract_id']."'
											and end_date <'".get_date_working()."' and project_status = 'open'
											AND contract_status<>'Ended'
											and delete_status='n'";
						$result_close_project = mysql_query($sql_close_project);
						if (mysql_num_rows($result_close_project)>0){
							$string_7 .= "7";
						}
					}
				}	
				//neu: khong cho nhap timesheet neu contract fee khac billing total
				if (($cit_contract_fee_onetime=='1') ||( $cit_fee_total_monthly=='1')){
					$sql_billing = "Select sum(bil_amount) as billing_total
										from  billings bl 
										where bil_contract_id ='".$row_contract['contract_id']."'
										and (bil_status='active' or bil_status='create')
										and bil_delete_status='n'";
				
						$result_billing = mysql_query($sql_billing);
						if (mysql_num_rows($result_billing)>0){
							$row_billing = mysql_fetch_array($result_billing);
							$billing_total = doubleval($row_billing['billing_total']);	
						}
						else {
							$billing_total = 0;
						}
			
					if (($row_contract_type['job_type']=="One time" ||($row_contract_type['job_type']=="cs" && $row_contract_type['cs_type']=="project")) && ($cit_contract_fee_onetime=='1')){
							if ($billing_total!= doubleval($row_contract['monthly_free'])){
								$string_8 .= "8";
							}
					}
					
				//neu: khong cho nhap timesheet neu fee total khac billing total(ap dung cho monthly contract)
					if (($cit_fee_total_monthly=='1') && ($row_contract_type['job_type']=="Monthly" || ($row_contract_type['job_type']=="cs" && $row_contract_type['cs_type']=='retainer'))){
						$sql_count_project = "select sum(monthly_free) as monthly_free from contract
											where con_id='".$row_contract['contract_id']."'
											and delete_status='n'";
						$result_count_project = mysql_query($sql_count_project);
						$row_fee_project = mysql_fetch_array($result_count_project);
						$fee_total = doubleval($row_fee_project['monthly_free']);
						if ($fee_total != $billing_total) {
							$string_10 .= "10";
						}
					}
				}
				
				//neu: khong cho nhap timesheet neu billing date nho hon ngay nhap
				if ($cit_billing_date=='1'){
					$sql_billing ="select * from billings
									where bil_date_plan<='".get_date_working()."'
									and bil_status ='active'
									and bil_delete_status = 'n'
									and bil_contract_id='".$row_contract['contract_id']."'";
					if (mysql_num_rows(mysql_query($sql_billing))>0){
						$string_9 .= "9";
					}
				}
				
				if ($cit_expire_after=='1'){
					$sql_condition ="select * from condition_input_timesheet";
					$result_condition = mysql_query($sql_condition); 
					$row_condition = mysql_fetch_array($result_condition);
					if (mysql_num_rows($result_condition)>0) {
						$cit_expire_after = $row_condition["cit_expire_after"];
					}
				}
			}
		}
	}
	
	
	
    if ($string_7!=""){
 		return 7;
 	}
    if ($string_8!=""){
 		return 8;
 	}
    if ($string_10!=""){
 		return 10;
 	}
    if ($string_9!=""){
 		return 9;
 	}
	if ($string_13!=""){
 		return 13;
 	}
	return 4;
}
//Hoang manh kiem

function getTimeFromIW_Dailydata($iw_id)
{
	$sqlGetTime="SELECT SUM(working_time) AS time_registed FROM daily_working_data_iw_temp WHERE(delete_status='n') AND (daily_working_data_iw_temp.date_regis<='".check_last_date_working()."') AND (iw_id='".$iw_id."') GROUP BY iw_id";
	
		$sqlGetTimeMaster="SELECT SUM(working_time) AS time_registed FROM daily_working_data_iw WHERE(delete_status='n') AND (daily_working_data_iw.date_regis<='".check_last_date_working()."') AND (iw_id='".$iw_id."') GROUP BY iw_id";
	$resultGetTimeMaster=mysql_query($sqlGetTimeMaster) or die(err_msg());
			
	$resultGetTime=mysql_query($sqlGetTime) or die(err_msg());
	$working_time_temp = 0;
	$working_time_master = 0;
	if(mysql_num_rows($resultGetTime)>0){
	
		$rowGetTime = mysql_fetch_array($resultGetTime);
		$working_time_temp = $rowGetTime["time_registed"];
	}
	
		if(mysql_num_rows($resultGetTimeMaster)>0){
	
		$rowGetTimeMaster = mysql_fetch_array($resultGetTimeMaster);
		$working_time_master = $rowGetTimeMaster["time_registed"];
	}
	
	return ($working_time_temp+$working_time_master);
	
	
}

/*
function getContactPersonOpen($user)
{
    $sql="SELECT * FROM contract where ((delete_status='n')and(contract_status<>'Ended'))and ((scs_contract_per2='".$user."')or(scs_contract_per1='".$user."'))";
	$result=mysql_query($sql); print mysql_error();
	return mysql_num_rows($result);
}
*/
//Start LinhLC-----------------------------------------------------------------------------------------------------------------------------------------------
//con la member cua hop dong chua end 1
function getContactPersonOpen($user)
{
    $sql_ct_mem = " SELECT DISTINCT contract_id FROM contract_member WHERE id_user='".$user."' AND delete_status='n' ";
    $qr = mysql_query($sql_ct_mem);
    if(mysql_num_rows($qr)>0){
        $temp_ct_id ='';
        while($row = mysql_fetch_assoc($qr)){
            $temp_ct_id .=$row['contract_id'].',';
        }
    }
    else{
        $temp_ct_id = '0,';
    }
    $ct_id = substr($temp_ct_id,0,-1);
    $sql="SELECT contract_id FROM contract WHERE contract_id IN(".$ct_id.") AND delete_status='n' AND contract_status<>'Ended' ";
	$result=mysql_query($sql); print mysql_error();
	return mysql_num_rows($result);
}

//check time sheet before disable user
//nguoi nay la leader cua contract, va ko con timesheet nao lien quan den contract do ma chua dc duyet 4
function getTimeSheetNotApprovalOfLeader($user_id,$contract_id=0)
{
		$dao = new DAO();
		$time_current = gmdate("His", time() + 7*3600);
		//check team leader first
		$sql = 'SELECT * FROM daily_working_data_temp';
		if($contract_id==0)
		{
		
			//$sql .= ' WHERE (Contact_id IN ())';
			$sql_all_contract = 'SELECT contract_id FROM contract WHERE scs_contract_per1="'.$user_id.'" AND delete_status="n" AND contract_status<>"Ended"';
			$num_time_sheet = 0;
			$result_temp = mysql_query($sql_all_contract);
			while($row=mysql_fetch_array($result_temp))
			{
				
				$sql_check_pr = 'SELECT contract_id FROM contract WHERE Con_id="'.$row["contract_id"].'" AND contract_status<>"Ended" AND delete_status="n"';
				
				//check if contract is monthly
				if(mysql_num_rows(mysql_query($sql_check_pr))>0)
				{
				
					$sql .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE  Con_id='.$row["contract_id"].'))';
					
				}
				else //one time
				{
				
					$sql .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE  contract_id='.$row["contract_id"].'))';
					
				}
				
				$sql .= ' AND (daily_working_data_temp.delete_status="n") AND (daily_working_data_temp.is_examined<>"yes")';
				
			
					$sql .= '  AND (daily_working_data_temp.date_regis<="'.get_date_to_check().'")';
			
				$result=mysql_query($sql);
				$num_time_sheet += mysql_num_rows($result);

				
			}
			
								
				return $num_time_sheet;
			
		}
		else
		{
			
			$sql_check_pr = 'SELECT contract_id FROM contract WHERE Con_id="'.$contract_id.'" AND contract_status<>"Ended" AND delete_status="n"';
			
			//check if contract is monthly
			if(mysql_num_rows(mysql_query($sql_check_pr))>0)
			{
			
				$sql .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE delete_status="n" AND contract_status<>"Ended" AND con_id='.$contract_id.'))';
				
			}
			else //one time
			{
			
				$sql .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE  delete_status="n" AND contract_status<>"Ended" AND contract_id='.$contract_id.'))';
				
			}
			
			$sql .= ' AND (daily_working_data_temp.delete_status="n") AND (daily_working_data_temp.is_examined<>"yes")';
			
					$sql .= '  AND (daily_working_data_temp.date_regis<="'.get_date_to_check().'")';
			
		}
		
		
		//echo $sql;
		$result=mysql_query($sql); print mysql_error();
		return mysql_num_rows($result);
}

//ko con timesheet lien quan den contract chua dc examined cho den ngay hom qua 6
function getTimeSheetNotApproval($user_id,$contract_id=0)
{
		$time_current = gmdate("His", time() + 7*3600);
		$sql = 'SELECT dwd_id FROM daily_working_data_temp';
		if($contract_id>0){
			$sql_check_pr = 'SELECT contract_id FROM contract WHERE Con_id="'.$contract_id.'" AND contract_status<>"Ended" AND delete_status="n"';
			$qr = mysql_query($sql_check_pr);
			//check if contract is monthly
			if(mysql_num_rows($qr)>0){
                    $temp_id = '';
                    while($row = mysql_fetch_assoc($qr)){
                        $temp_id .=$row['contract_id'];
                    }
                $ct_id = substr($temp_id,0,-1);
				$sql .= ' WHERE Contact_id IN ('.$ct_id.') ';	
			}
			else {//one time
				$sql .= ' WHERE Contact_id ="'.$contract_id.'" ';
				
			}
			$sql .= '  AND daily_working_data_temp.delete_status="n" AND user_id="'.$user_id.'" AND daily_working_data_temp.is_examined<>"yes" ';
			$sql .= '  AND daily_working_data_temp.date_regis<="'.get_date_to_check().'" ';
			$result=mysql_query($sql); print mysql_error();
			return mysql_num_rows($result);
			
		}
		$sql .= ' WHERE daily_working_data_temp.delete_status="n"  AND user_id="'.$user_id.'" AND daily_working_data_temp.is_examined<>"yes" ';
		$sql .= '  AND  daily_working_data_temp.date_regis<="'.get_date_to_check().'" ';
		$result=mysql_query($sql); print mysql_error();
		return mysql_num_rows($result);
		
}







//ThangNT - start
//la thanh vien cua mot group 8
function checkUserInGroup($user_id)
{
        $get_group = " SELECT DISTINCT gm_g_id FROM group_members WHERE gm_delete_status='n' AND gm_user_id='".$user_id."'";
        $qr = mysql_query($get_group);
        if(mysql_num_rows($qr)>0){
            $temp_g_id = '';
            while($row = mysql_fetch_assoc($qr)){
                $temp_g_id .=$row['gm_g_id'].',';
            }
        }else{
            $temp_g_id ='0,';
        }
        $g_id = substr($temp_g_id,0,-1);
		$sql = " SELECT g_id  FROM groups  WHERE g_id IN (".$g_id.") AND g_delete_status='n' ";
		$result=mysql_query($sql); print mysql_error();
		return mysql_num_rows($result);
}

//la mot step cua mot workflow nao do 7
function checkUserInWorkflow($user_id)
{       
        $get_group = " SELECT DISTINCT gm_g_id FROM group_members WHERE gm_delete_status='n' AND gm_user_id='".$user_id."'";
        $qr = mysql_query($get_group);
        if(mysql_num_rows($qr)>0){
            $temp_g_id = '';
            while($row = mysql_fetch_assoc($qr)){
                $temp_g_id .=$row['gm_g_id'].',';
            }
        }else{
            $temp_g_id ='0,';
        }  
        $g_id = substr($temp_g_id,0,-1); 
		$sql = "SELECT wfs_id FROM workflow_steps WHERE wfs_delete_status='n' AND (wfs_user_id=".$user_id." OR wfs_g_id IN (".$g_id.") )";
		$result=mysql_query($sql); print mysql_error();
		return mysql_num_rows($result);
}

/*
function checkRequestDelegation($user_id)
{
		$sql = '	SELECT * FROM request_steps WHERE rs_delete_status="n" AND rs_status="delegate" AND rs_user_id='.$user_id;
		$result=mysql_query($sql); print mysql_error();
		return mysql_num_rows($result);
}
*/


//ThangNT - end

/*
function getContactPersonOpenList($user)
{
    $sql="SELECT * FROM contract where ((delete_status='n')and(contract_status<>'Ended'))and ((scs_contract_per2='".$user."')or(scs_contract_per1='".$user."'))";
	return $sql;
}
*/
//	Check when change PIC and PIC is leader of clients'contracts
function checkClientHaveAnyContract($client_id)
{
	$sql_contract_by_client = 'SELECT contract_id FROM contract';
	$sql_contract_by_client .= ' WHERE Client_id="'.$client_id.'" AND contract_status<>"Ended" AND delete_status="n"';
	$result = mysql_query($sql_contract_by_client);
	if(mysql_num_rows($result)>0)
	{
		return mysql_num_rows($result);
	}
		
	return 0;
	 
}


function checkPicReview($client_id , $is_pic){
	$total_ts = 0;
	$sql_search_ct = "SELECT contract_id
				FROM contract ct, contract_type cp
				WHERE ct.contract_type_id = cp.contract_type_id
				AND (
				(
				ct.con_id <>0
				AND job_type = 'Monthly'
				)
				OR (
				ct.con_id =0
				AND job_type = 'One time'
				)
                OR (
				ct.con_id =0
				AND job_type = 'cs'
				)
				)
				AND (
				(ct.client_id = ".$client_id." and ct.client_id_2 = 0)
				OR ct.client_id_2 = ".$client_id."
				)
				AND ct.contract_status <> 'Ended'
				AND ct.delete_status = 'n'";
	//truoc 8h: kiem tra ngay lam viec ke truoc nua
	//sau 8h: kiem tra ngay lam viec ke truoc
	
	
		$sql_search_ts =  "SELECT * from daily_working_data where Contact_id IN (".$sql_search_ct.") and delete_status='n' AND pic".$is_pic."_review <> 'Yes' and date_regis <= '".get_date_to_check()."'";
	$sql_search_ts .= "UNION ALL SELECT * from daily_working_data_temp where Contact_id IN (".$sql_search_ct.") and delete_status='n' AND pic".$is_pic."_review <> 'Yes' and date_regis <= '".get_date_to_check()."'";
	$query_search_ts = mysql_query($sql_search_ts);
	$total_ts += mysql_num_rows($query_search_ts);
	return $total_ts;
}


/*function checkPicReview($client_id,$pic_id,$is_pic1)
{
	
	if($pic_id==0)
	{
		return 0;
	}
	
	$sql_contract_by_client = 'SELECT contract_id,con_id FROM contract';
	$sql_contract_by_client .= ' WHERE Client_id="'.$client_id.'" AND Client_id_2="0" AND contract_status<>"Ended" AND delete_status="n"';
	$sql_contract_by_client .= 'UNION ALL SELECT contract_id,con_id FROM contract';
	$sql_contract_by_client .= ' WHERE Client_id_2="'.$client_id.'" AND contract_status<>"Ended" AND delete_status="n"';
	echo $sql_contract_by_client;
	
	$result = mysql_query($sql_contract_by_client);
	$num_time_sheet_not_confirm = 0;
	while($row=mysql_fetch_array($result))
	{
		$sql = 'SELECT * FROM daily_working_data_temp';
		$sql_master = 'SELECT * FROM daily_working_data';
		$sql_check_pr = 'SELECT contract_id FROM contract WHERE con_id="'.$row["contract_id"].'" AND contract_status<>"Ended" AND delete_status="n"';
			
			//check if contract is monthly
			if(mysql_num_rows(mysql_query($sql_check_pr))>0)
			{
			
		$sql .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE delete_status="n" AND contract_status<>"Ended" AND con_id='.$row["contract_id"].'))';
		$sql_master .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE delete_status="n" AND contract_status<>"Ended" AND con_id='.$row["contract_id"].'))';
				
			}
			else //one time
			{
			
				$sql .= ' WHERE (Contact_id ="'.$row["contract_id"].'")';
				$sql_master .= ' WHERE (Contact_id ="'.$row["contract_id"].'")';
				
			}
			
			if($is_pic1)
			{
				$sql .= '  AND (daily_working_data_temp.delete_status="n") AND (daily_working_data_temp.pic1_review<>"yes")';
				$sql_master .= '  AND (daily_working_data.delete_status="n") AND (daily_working_data.pic1_review<>"yes")';
			}
			else
			{
				$sql .= '  AND (daily_working_data_temp.delete_status="n") AND (daily_working_data_temp.pic2_review<>"yes")';
				$sql_master .= '  AND (daily_working_data.delete_status="n") AND (daily_working_data.pic2_review<>"yes")';
			}
			$sql .= '  AND (daily_working_data_temp.date_regis<="'.check_last_date_working().'")';	
			$sql_master .= '  AND (daily_working_data.date_regis<="'.check_last_date_working().'")';	
			echo $sql.'</br>';
		//	echo $sql_master.'</br>';
			
			$result_tmp = mysql_query($sql); print mysql_error();
			$result_master = mysql_query($sql_master); print mysql_error();
		//	echo mysql_num_rows($result_tmp).'----'.mysql_num_rows($result_master);
			$num_time_sheet_not_confirm += mysql_num_rows($result_tmp)+mysql_num_rows($result_master);
			$sql = '';
			$sql_master = '';
			
	}
	
	return $num_time_sheet_not_confirm;
	
}*/

function isPIC1($client_id,$pic_id){
	
	$sql = 'SELECT pic1,pic2 FROM client_list WHERE Client_id='.$client_id.' AND delete_status="n"';
	$rs = mysql_query($sql);
	$row = mysql_fetch_array($rs);
	if($row["pic1"]==$pic_id){
		return 1;
	}
	
	return 0;

}

function checkEditInternalTask($iw_id,$branch_id,$id_user,$is_leader)
{
	$total_result = 0;
	$total_master = 0;
	$time_current = gmdate("His", time() + 7*3600);
	$sql_check = 'SELECT * FROM daily_working_data_iw_temp AS diw INNER JOIN internal_work_manger as iwm ON diw.iw_id=iwm.iw_id';
	$sql_check_master = 'SELECT * FROM daily_working_data_iw AS diw INNER JOIN internal_work_manger as iwm ON diw.iw_id=iwm.iw_id';
	if($is_leader)
	{
		$sql_check .= ' WHERE iwm.id_leader="'.$id_user.'" AND iwm.delete_status="n" AND diw.delete_status="n"';
		$sql_check .= ' AND diw.iw_id="'.$iw_id.'" AND diw.is_examined<>"yes"';		
	}
	else
	{
		$sql_check .= ' WHERE iwm.id_observer="'.$id_user.'" AND iwm.delete_status="n" AND diw.delete_status="n"';
		$sql_check .= ' AND diw.iw_id="'.$iw_id.'" AND diw.review<>"yes"';
		//get time sheet from master table
		$sql_check_master .= ' WHERE iwm.id_observer="'.$id_user.'" AND iwm.delete_status="n" AND diw.delete_status="n"';
		$sql_check_master .= ' AND diw.iw_id="'.$iw_id.'" AND diw.review<>"yes"';
		$sql_check_master .= ' AND diw.branch_id="'.$branch_id.'"';
		//truoc 8h thi kiem tra ngay lam viec ke truoc nua
		//sau 8h chi kiem tra ngay lam viec ke truoc
		
		$sql_check_master .= '  AND (diw.date_regis<="'.get_date_to_check().'")';	
		
		
		$result_master = mysql_query($sql_check_master);
		$total_master = mysql_num_rows($result_master);	
				
	}	
		$sql_check .= ' AND diw.branch_id="'.$branch_id.'"';
		$sql_check .= '  AND (diw.date_regis<="'.get_date_to_check().'")';	
		
				
//	echo $sql_check;
//	echo '<br/>'.$sql_check_master;	
	$result = mysql_query($sql_check);
	$total_result = mysql_num_rows($result);	
	return ($total_result+$total_master);
}

function show_time_sheet($contract_id){

		$dao = new DAO();
		$sql = 'SELECT * FROM daily_working_data_temp INNER JOIN tbl_users ON daily_working_data_temp.user_id=tbl_users.id_user';
			
		$sql_check_pr = 'SELECT contract_id FROM contract WHERE Con_id="'.$contract_id.'" AND contract_status<>"Ended" AND delete_status="n"';
		
		//check if contract is monthly
		if(mysql_num_rows(mysql_query($sql_check_pr))>0)
		{
		
			$sql .= ' WHERE (Contact_id IN (SELECT contract_id FROM contract WHERE delete_status="n" AND contract_status<>"Ended" AND con_id='.$contract_id.'))';
			
		}
		else //one time
		{
		
			$sql .= ' WHERE (Contact_id ="'.$contract_id.'")';
			
		}
		
		$sql .= '  AND (daily_working_data_temp.delete_status="n")  AND (daily_working_data_temp.is_examined<>"yes")';
		
		$result = $dao->query($sql);
		
		return $result;
		
}
/*End LinhLC--------------------------------------------------------------------------------------------------------------------------------------------------------*/
function getContactPersonOpenList($user)
{
     $pc_sql = "SELECT DISTINCT contract_id FROM contract_member WHERE id_user='".$user."' AND delete_status='n'";
     $qr = mysql_query($pc_sql);
     if(mysql_numrows($qr)){
        $temp_id = '';
        while($row = mysql_fetch_array($qr)){
            $temp_id .= $row['contract_id'].',';
        }
     }else{
        $temp_id = '0,';
     }
     $user_id = substr($temp_id,0,-1);
     $sql="SELECT contract_no FROM contract WHERE contract_id IN(".$user_id.") AND (delete_status='n') AND (contract_status<>'Ended')";
	return $sql;	
}



function getContactPerson($user)
{
    $sql="SELECT * FROM contract where  delete_status='n' and( scs_contract_per2='".$user."' or scs_contract_per1='".$user."' )";
	$result=mysql_query($sql); print mysql_error();
	return mysql_num_rows($result);
}


function getWorkingHourUser($user)
{
	$sqlGetTimeWorkingData="select sum(working_time) as time_registed from daily_working_data where delete_status='n' and user_id='".$user."'  group by user_id";
	$resultGetTimeWorkingData=mysql_query($sqlGetTimeWorkingData) or die(err_msg());
	$rowGetTimeWorkingData=mysql_fetch_array($resultGetTimeWorkingData);
	$TimeWorkingData=$rowGetTimeWorkingData["time_registed"];
	
	
	$sqlGetTimeInternalData="select sum(working_time) as time_registed from daily_working_data_iw where delete_status='n' and user_id='".$user."'   group by user_id";
	$resultGetTimeInternalData=mysql_query($sqlGetTimeInternalData) or die(err_msg());
	$rowGetTimeInternalData=mysql_fetch_array($resultGetTimeInternalData);
	$TimeInternalData=$rowGetTimeInternalData["time_registed"];
	$WorkingHour=$TimeWorkingData+$TimeInternalData;
	return $WorkingHour; 
}

function checkTimeSheetForClient($client)
{
    $sql="SELECT SUM(working_time) as timesheet FROM contract inner join daily_working_data on daily_working_data.Contact_id=contract.contract_id   where  client_id='".$client."'  group by client_id";
	$result=mysql_query($sql);
	$GetTime=mysql_fetch_array($result);
	return $GetTime["timesheet"];
}

//get service by client 
//Check condition to delete client for sale management
function getServiceByClient($client_id){

	$sql = 'SELECT * FROM quotation_for_service WHERE qfs_client_id='.$client_id.' AND qfs_delete_status="n"';
	$result=mysql_query($sql);
	$num_row = mysql_num_rows($result);
	
	return $num_row;
}

function getClientInContract($client)
{
    $sql="SELECT * FROM contract where (delete_status='n')and(client_id='".$client."')";
	$result=mysql_query($sql);
	return mysql_num_rows($result);	
}


function getJopType($contract_type_id)
{
	$sql="select * from contract_type  where contract_type_id='".$contract_type_id."'";
	$result=mysql_query($sql);
	$row=mysql_fetch_array(mysql_query($sql));
	if(mysql_num_rows($result)>0)
	{
		$job_type=$row["job_type"];
	}	
	return $job_type;	
}

function getCsType($contract_type_id)
{
	$sql="select * from contract_type where contract_type_id='".$contract_type_id."'";
	$result=mysql_query($sql);
	$row=mysql_fetch_array(mysql_query($sql));
	if(mysql_num_rows($result)>0)
	{
		$job_type=$row["cs_type"];
	}	
	return $job_type;	
}

function getEnddateOfmonth($dateregit)
{
	$i=substr($dateregit,5,2);

	switch ($i) {
	case '01':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;
	case '03':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;
	case '05':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;
	case '07':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;
	case '08':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;
	case '10':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;
	case '12':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-31";
		break;	
	
	case '04':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-30";
		break;	
		
	case '06':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-30";
		break;				
	
	case '09':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-30";
		break;	
		
	case '11':
		return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-30";
		break;				
	
	case '02':
		
		if(substr($dateregit,0,4)%4==0)
		{
			return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-29";
		}else
			return substr($dateregit,0,4)."-".substr($dateregit,5,2)."-28";
		break;		

	}
}





function getChargeRate($dateregit, $id_user)
{
	$sql="select charge_rate from charge_rate where id_user ='".$id_user."' and cr_date<='".$dateregit."' order by cr_date desc";
	$result=mysql_query($sql);
	$row=mysql_fetch_array($result);
	if(mysql_num_rows($result)>0)
	{ 
		return $row["charge_rate"];
	}else
	return 0;
	
}

function getChargeRateContract($contract_id, $id_user)
{
	$sql_leader = "select * from contract where contract_id = ".$contract_id."";
	$result_leader = mysql_query($sql_leader);
	$row_leader = mysql_fetch_array($result_leader, MYSQL_BOTH);
	if($row_leader['con_id']==0)
	{
		$date_start = $row_leader['start_date'];
	}else
	{
		$sql_leader_name = "select * from contract where contract_id = ".$row_leader['con_id']."";
		$result_leader_name = mysql_query($sql_leader_name);
		$row_leader_name = mysql_fetch_array($result_leader_name, MYSQL_BOTH);
		$date_start = $row_leader_name['start_date'];
	}
	$sql="select * from charge_rate where (id_user ='".$id_user."') and (cr_date<='".$date_start."') order by cr_date desc";
	$result=mysql_query($sql);
	$row=mysql_fetch_array($result);
	if(mysql_num_rows($result)>0)
	{ 
		return $row["charge_rate"];
	}else
	{	
		$sql="select * from charge_rate where (id_user ='".$id_user."') order by cr_date";
		$result=mysql_query($sql);
		$row=mysql_fetch_array($result);
		return $row["charge_rate"];
	}	
}

function getContent($dateregit,$id_user,$contract_id,$mode)
{
	
	if($mode==1)
	{
		$sql="select * from daily_working_data  where (user_id ='".$id_user."')and(date_regis='".$dateregit."')and(Contact_id='".$contract_id."') order by date_regis desc";
	}
	
	if($mode==2)
	{
		$sql="select * from daily_working_data_iw where (user_id ='".$id_user."')and(date_regis='".$dateregit."')and(iw_id='".$contract_id."') order by date_regis desc";
	}
	
	$result=mysql_query($sql);
	$row=mysql_fetch_array($result);
	if(mysql_num_rows($result)>0)
	{ 
		return $row["content"];
	}else
	return "";
	
}
	/*
	 * function name: insertSalonNewRegist
	 * @Input: mysqlConnection $con, varchar $salon_name
	 * @Return: boolean
	 * return false if existed
	*/
	function getNewC_ID($numb)
	{
		//get new C_ID by format (0000000#####)
		if(strlen($numb)<11){
			$prefix = 11-strlen($numb);
			for($i=0;$i<$prefix;$i++)
				$numb = "0".$numb;
		}
			return $numb;
	}




	function getEndDateProject($contract_id)
	{
		$sql_pr="select * from contract where con_id='".$contract_id."' order by end_date desc";
		$result_pr=mysql_query($sql_pr) or die(err_msg());
		$row_pr=mysql_fetch_array(mysql_query($sql_pr));
		if(mysql_num_rows($result_pr)>0)
		{
			$end_date=$row_pr["end_date"];
		}else
		{
			$end_date="";
		}
		return $end_date;
	}
	

	function last_time_sheet_date($project_tm)
	{
		$sql_pr="SELECT * FROM daily_working_data WHERE Contact_id='".$project_tm."' AND delete_status='n' AND date_regis <= '".date('Y-m-d')."' ORDER BY date_regis DESC";

		$result_pr=mysql_query($sql_pr) or die(err_msg());
		$row_pr=mysql_fetch_array(mysql_query($sql_pr));
		$end_date = "";
		if(mysql_num_rows($result_pr)>0)
		{
			$end_date=$row_pr["date_regis"];
		}
		
		return $end_date;
	}
	
	function check_condition_close_prj($project_tm){
	
		$sql_pr="SELECT * FROM daily_working_data_temp 
		WHERE Contact_id='".$project_tm."' 
		AND delete_status='n' AND is_examined!='yes'
		AND date_regis <= '".get_date_to_check()."' ORDER BY date_regis DESC";

		$result_pr=mysql_query($sql_pr) or die(err_msg());
		$row_pr=mysql_fetch_array(mysql_query($sql_pr));
		
		return mysql_num_rows($result_pr);
	}
	
	
	function last_time_sheet_date_monthly($project_tm)
	{
		$sql_pr="select * from daily_working_data inner join contract on(daily_working_data.Contact_id=contract.contract_id) where con_id='".$project_tm."' and daily_working_data.delete_status='n' and date_regis <= '".date('Y-m-d')."' order by date_regis desc";
		$result_pr=mysql_query($sql_pr) or die(err_msg());
		$row_pr=mysql_fetch_array(mysql_query($sql_pr));
		if(mysql_num_rows($result_pr)>0)
		{
			$end_date=$row_pr["date_regis"];
			/*
			while($row_pr=mysql_fetch_array($result_pr))
			{
				
				if($row_pr["is_examined"]=="yes")
				{
					$end_date = $row_pr["date_regis"];
					//echo $end_date.'<br/>';
				}
			
				else
				{
					break;
				}
				
			}
			*/
		}else
		{
			$end_date="";
		}
		return $end_date;
	}



	
	
	
	function getvalueofcontract($con_id)
	{
		$sql_co="select monthly_free from contract where (contract_id='".$con_id."')and(delete_status='n')";
		$result_co=mysql_query($sql_co) or die(err_msg());
		$row_co=mysql_fetch_array(mysql_query($sql_co));
		if(mysql_num_rows($result_co)>0)
		{
			$value=$row_co["monthly_free"];
		}		
		return $value;
	}		
	
	
	function gettimesheetprevius($con_id,$year_month)
	{
		
		$sql_pr="select sum(working_time*charge_rate_current) as total_working from daily_working_data inner join contract on(daily_working_data.Contact_id=contract.contract_id) where (con_id='".$con_id."')and(date_regis<'".$year_month."-01')and(contract.delete_status='n') group by con_id ";
		$result_pr=mysql_query($sql_pr) or die(err_msg());
		$row_pr=mysql_fetch_array(mysql_query($sql_pr));
		if(mysql_num_rows($result_pr)>0)
		{
			$total_working=$row_pr["total_working"];
		}
		
		return getvalueofcontract($con_id)-$total_working;
	}	
	
	
	
	function getbudgetofinternal($iw_id,$year_month,$branch_id=0)
	{
		
		$sql_pr="select bdg_budget from budget where (iw_id='".$iw_id."')and(bdg_month<='".$year_month."')and(delete_status='n') order by bdg_month desc ";
		if($branch_id>0){
		
			$sql_pr="select bdg_budget from budget where (iw_id='".$iw_id."')and(bdg_month<='".$year_month."') and(branch_id='".$branch_id."') and(delete_status='n') order by bdg_month desc ";
		}
		$result_pr=mysql_query($sql_pr);
		$row_pr=mysql_fetch_array(mysql_query($sql_pr));
		if(mysql_num_rows($result_pr)>0)
		{
			$bdg_budget=$row_pr["bdg_budget"];
		}
		return $bdg_budget;
	}	
	
	function replaceslash($str)
	{
		$temp = ereg_replace('\+','',$str);
		return $temp;
	}
	
function getClientCheckTimeSheetSCS41DWDT($client_id)
{
$sql_dwdt = 'SELECT daily_working_data_iw.iw_id from daily_working_data_iw inner join sub_internal_work ON daily_working_data_iw.iw_id = sub_internal_work.siw_id 
 WHERE sub_internal_work.siw_sub_code="'.SALE_TASK.'" 
 AND daily_working_data_iw.client_id="'.$client_id.'"
 AND daily_working_data_iw.delete_status="n"
 UNION
 SELECT daily_working_data_iw_temp.iw_id from daily_working_data_iw_temp inner join sub_internal_work ON daily_working_data_iw_temp.iw_id = sub_internal_work.siw_id 
 WHERE sub_internal_work.siw_sub_code="'.SALE_TASK.'" 
 AND daily_working_data_iw_temp.client_id="'.$client_id.'"
 AND daily_working_data_iw_temp.delete_status="n" ';
$run_sql_dwdt = mysql_query($sql_dwdt);
return  mysql_num_rows($run_sql_dwdt);
}
############################################ WorkFlow Approval RFA ##########################################
#use => waiting for approval rfa
//Begin
function show_all($module)
	{
		
		$sql = "select * from requests where r_status!='approval' and r_delete_status='n' and r_module_id='".$module."'";
		return $sql;
	}
	function check_edit_delete_all($id,$module)
	{
	$sql = "select * from requests where r_status='rejected' and r_delete_status='n' and r_form_id='".$id."' and r_module_id='".$module."' and r_register_user=".$_COOKIE["sys_idMember"];
	$runqr = mysql_query($sql);
	if(mysql_num_rows($runqr)>0)
	{
		return 'right';
	}
	else
	{
		return 'left';
	}
	}
    function remaincheck($id)
    {
    $sql = "select * from requests where r_delete_status='n' and r_form_id=".$id;
	$runqr = mysql_query($sql);
	if(mysql_num_rows($runqr)>0)
	{
		return 'right';
	}
	else
	{
		return 'left';
	}
    
    }
    
    
	function check_view_all($id)
	{
	$sql = "select * from requests where r_status='waiting' and r_delete_status='n' and r_form_id='".$id."'";
	$runqr = mysql_query($sql);
	if(mysql_num_rows($runqr)>0)
	{
		return 'right';
	}
	else
	{
		return 'left';
	}	
	}
	
	function show_detail($tb_module,$where,$id,$status )
	{
		$sql = "select * from ".$tb_module." where ".$where." in (".$id.") and ".$status."='n'";
		return $sql;
	}
	
	function show_user_incharge($id)
	{
		$sql = "select full_name from tbl_users where id_user = ".$id." and status='Enable' and delete_status='n' ";
		$rs = mysql_query($sql);
		$row = mysql_fetch_array($rs);
		if(mysql_num_rows($rs)>0)
		{
			return $row['full_name'];
		}
		else
		{
			return '';
		}
	}
	function show_name_branch($id)
	{
		$sql = "select branch_Name,short_name from branch where branch_id = ".$id." and delete_status ='n' ";
		$rs = mysql_query($sql);
		$row = mysql_fetch_array($rs);
		if(mysql_num_rows($rs)>0)
		{
			return $row['short_name'];
		}
	}

//End
#use for content mail
//Begin
///Ham su dung cho function all_mail_for_contract
function _getTimeCharge($contract_id){
    //add more infarmation service fee, time charge, discount
	$sql_timecharge = " SELECT sum(charge_rate_current * working_time ) as time_charge
						FROM daily_working_data dwd WHERE is_examined='yes' and dwd.delete_status='n' and dwd.contact_id = ".$contract_id;

	//echo $sql_timecharge;																		
	$rs_time_charge = mysql_query($sql_timecharge) or die(mysql_error());					
	$rc = mysql_fetch_object($rs_time_charge);
	$time_charge = $rc->time_charge;
    return (0.0+$time_charge);
}
function _discountRateActual($timecharge, $fee){
    if($timecharge == 0){
        return 'N/A';
    }else{
        $discount = $timecharge - $fee;
        $percent_discount_rate = $discount/$timecharge*100;
        return number_format($percent_discount_rate,2,'.',',').' %';
    }
}
function all_mail_for_contract($id)
{
    $dao = new DAO();
	$str = '
            <html>
            <head>
        	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
        	<meta name="author" content="luyenlv" />
            <style>
            table.contacts
            { width: 100%;
            background-color: #fafafa;
            border: 1px #6699CC solid;
            border-collapse: collapse;
            border-spacing: 0px; }
            
            td.contactDept
            { background-color: #99CCCC;
            border: 1px #6699CC solid;
            font-family: Verdana;
            font-weight: bold;
            font-size: 12px;
            color: #404040;}
            
            td.contact
            { border: 1px #6699CC dotted;
            text-align: left;
            font-family: Verdana, sans-serif, Arial;
            font-weight: normal;
            font-size: .7em;
            color: #404040;
            background-color: #fafafa;
            padding-top: 4px;
            padding-bottom: 4px;
            padding-left: 8px;
            padding-right: 0px;}
            </style>
            </head>
            <body>
            <table class="contacts" cellspacing="0" summary="Contacts template">
            
            <tr><table class="contacts" cellspacing="0" summary="Contacts template"><tr>
            <td class="contactDept" colspan="2">Infomation Detail</td></tr>
            
            ';
		$contract_id = $id;
		$sql = "SELECT ct.contract_id, ct.contract_no, ct.start_date, ct.schedule_renew_date, ct.end_date, ct.monthly_free, ct.contract_status, ct.finish_reason, ct.check_list_src, ct.check_list_status, ct.memo, cp.contract_type_Name, cp.short_name, cp.job_type, cp.cs_type, br.branch_Name, us1.full_name AS team_leader, us2.full_name AS vice_team_leader, cl1.Client_name_in_English  AS client1, cl2.Client_name_in_English  AS client2
				FROM (
				contract ct
				INNER JOIN contract_type cp ON ct.contract_type_id = cp.contract_type_id
				INNER JOIN branch br ON ct.branch_id = br.branch_id
				INNER JOIN tbl_users us1 ON ct.scs_contract_per1 = us1.id_user
				LEFT JOIN tbl_users us2 ON ct.scs_contract_per2 = us2.id_user
				INNER JOIN client_list cl1 ON ct.client_id = cl1.client_id
				LEFT JOIN client_list cl2 ON ct.client_id_2 = cl2.client_id
				)
				WHERE contract_id ='".$contract_id."'";
		$query = mysql_query($sql);
		$row = mysql_fetch_assoc($query);
		//******************************************************/
 	    $str .='<tr>
                <td class="contact" width="200px">Contract No:</td>
                <td class="contact" >'.$row['contract_no'].'</td>
                </tr>';
    	//-Branch
        
        $str .='<tr>
                    <td class="contact" width="200px">Branch:</td>
                    <td class="contact">'.$row['branch_Name'].'</td>
                </tr>';
    	if($row['client2'] != ''){
    			//$str .= "- Client 1: ".$row['client1']."<br>";
                $str .='<tr>
                        <td class="contact" width="200px">Client 1:</td>
                        <td class="contact">'.$row['client1'].'</td>
                    </tr>';
    			//$str .= "- Client 2: ".$row['client2']."<br>";
                $str .='<tr>
                        <td class="contact" width="200px">Client 2:</td>
                        <td class="contact" >'.$row['client2'].'</td>
                    </tr>';
    		}else{
    			//$str .= "- Client: ".$row['client1']."<br>";
                $str .='<tr>
                        <td class="contact" width="200px">Client:</td>
                        <td class="contact" >'.$row['client1'].'</td>
                    </tr>';
    		}
    	//-
    	$str .='<tr>
                    <td class="contact" width="200px">Contract Type:</td>
                    <td class="contact" >'.$row['short_name']." :: ".$row['contract_type_Name'].'</td>
                </tr>';
    	//-
    	$str .='<tr>
                    <td class="contact" width="200px">SCS Team Leader:</td>
                    <td class="contact" >'.$row['team_leader'].'</td>
                </tr>';
    	//-
    	$str .='<tr>
                    <td class="contact" width="200px">SCS Vice Team Leader:</td>
                    <td class="contact" >'.$row['vice_team_leader'].'</td>
                </tr>';
    	//-
    	$str .='<tr>
                    <td class="contact" width="200px">SCS Member:</td>
                    <td class="contact">';
        $sql_member = "SELECT distinct us.full_name
    						FROM contract_member cm
    						INNER JOIN tbl_users us ON ( cm.id_user = us.id_user )
    						WHERE cm.contract_id = '".$contract_id."' AND cm.delete_status = 'n'
    						AND us.full_name <> '".$row['team_leader']."' AND us.full_name <> '".$row['vice_team_leader']."'";
    	$query_member = mysql_query($sql_member);
    	$count = 1;
    	while($row_member = mysql_fetch_assoc($query_member)){
    		$str .=$count.'. '.$row_member['full_name']."<br>";
    		$count++;
    	}
        $str .='</td></tr>';
    	if($row['job_type']=='One time' || ($row['job_type']=='cs' && $row['cs_type']=='project')){
    		//$str .= "- Contract Fee(USD): ";
            $str .='<tr>
                    <td class="contact" width="200px">Contract Fee(USD):</td>';
    	}else{
    		//$str .= "- Monthly Fee(USD): ";
            $str .='<tr>
                    <td class="contact" width="200px">Monthly Fee(USD):</td>';
    	}
    	//-Monthly Fee (Contract Fee(USD))
    	//$str .= number_format($row['monthly_free'])."<br>";
        $str .='<td class="contact" >'.number_format($row['monthly_free']).'</td>
                </tr>';
    	//-
    	//$str .= "- Start date: ".$row['start_date']."<br>";
        $str .='<tr>
                    <td class="contact" width="200px">Start date:</td>
                    <td class="contact" >'.$row['start_date'].'</td>
                </tr>';
    	//-
    	if($row['job_type']=='Monthly' || ($row['job_type']=='cs' && $row['cs_type']=='retainer')){
    		if($row['schedule_renew_date']=='0000-00-00'){
    			//$str .= "- Schedule renew date: <br>";
                $str .='<tr>
                            <td class="contact" width="200px">Remind date:</td>
                            <td class="contact" ></td>
                        </tr>';
    		}else{
    			//$str .= "- Schedule renew date: ".$row['schedule_renew_date']."<br>";
                $str .='<tr>
                            <td class="contact" width="200px">Remind date:</td>
                            <td class="contact" >'.$row['schedule_renew_date'].'</td>
                        </tr>';
    		}
    	}
    	//-
    	if($row['contract_status'] == 'Ended'){
    		//$str .= "- End date (Actual): ".$row['end_date']."<br>";
            $style_color ='style="color:red;"';
            $str .='<tr>
                        <td class="contact" width="200px">End date (Actual):</td>
                        <td class="contact" style="color:red;">'.$row['end_date'].'</td>
                    </tr>';
    	}else{
    	    $style_color ='';
    		if($row['end_date']=='0000-00-00'){
    			//$str .= "- End date (Plan): <br>";
                $str .='<tr>
                            <td class="contact" width="200px">End date (Plan):</td>
                            <td class="contact"></td>
                        </tr>';
    		}else{
    			//$str .= "- End date (Plan): ".$row['end_date']."<br>";
                $str .='<tr>
                            <td class="contact" width="200px">End date (Plan):</td>
                            <td class="contact">'.$row['end_date'].'</td>
                        </tr>';
    		}
    	}
    	//-
    	//$str .= "- Contract status: ".$row['contract_status']."<br>";
        $str .='<tr>
                    <td class="contact" width="200px">Contract status:</td>
                    <td class="contact"'.$style_color.'>'.$row['contract_status'].'</td>
                </tr>';
    	//-
    	//$str .= "- Check List: ".strtoupper($row['check_list_status'])."<br>";
        $str .='<tr>
                    <td class="contact" width="200px">Check List:</td>
                    <td class="contact">'.strtoupper($row['check_list_status']).'</td>
                </tr>';
        $str .='</table></tr>';
    	//-
    	//$str .= "- Finish reason: ".$row['finish_reason']."<br>";
    	//-
    	//$str .= "- Memo: ".$row['memo']."<br>";
        //****************Hop dong Monthly**********************************//
        if($row['job_type']=='Monthly' || ($row['job_type']=='cs' && $row['cs_type']=='retainer')){
            $str .='<tr><table class="contacts" cellspacing="0" summary="Contacts template"><tr>
                        <td class="contactDept" colspan="7">Billing Information</td>
                        </tr>';
            $str .='<tr>
                            <td class="contact" width="30px"><b>No</b></td>
                            <td class="contact"><b>Description</b></td>
                            <td class="contact"><b>Amount</b></td>
                            <td class="contact" width="100px"><b>Billing Date (Plan)</b></td>
                            <td class="contact" width="100px"><b>Billing Date (Actual)</b></td>
                            <td class="contact" width="100px"><b>Cancel Date</b></td>
                            <td class="contact"><b>Debit Note</b></td>
                    </tr>';

            $sql_billing_list = "SELECT *
                FROM billings
                LEFT JOIN tbl_debit_note ON ( bil_debitnote_id = dbn_id )
                WHERE bil_contract_id = '".$contract_id."'
                AND bil_status <> 'update'
                AND bil_delete_status = 'n'
                ORDER BY bil_date_plan";
            $query_billing_list = mysql_query($sql_billing_list);
            $x_prj = 1;
            while($row_billing_list = mysql_fetch_assoc($query_billing_list)){
                $style_all = "style='text-decoration: none'";
                if($row_billing_list['bil_status']=='cancel'){
                    $underline = 'style="text-decoration: line-through;color:red;"';
                    $cancel = 'style="text-decoration: none"';
                }else{
                    $underline = '';
                    $cancel = '';
                }
                $str .='<tr >
                            <td class="contact" width="30px" '.$underline.'>'.$x_prj.'</td>
                            <td class="contact" '.$underline.'>'.$row_billing_list['bil_description'].'</td>
                            <td class="contact" '.$underline.'>'.$row_billing_list['bil_amount'].'</td>
                            <td class="contact" width="100px" '.$underline.'>'.$row_billing_list['bil_date_plan'].'</td>';
                $str .='<td class="contact" width="100px" '.$underline.'>';
                if($row_billing_list['dbn_id'] != ''){
                            $str .=$row_billing_list['bil_date_actual'];
                }
                $str .='</td>';
                $str .='<td class="contact" width="100px" '.$cancel.'>';
                if($row_billing_list['status'] == 'cancel'){
                            $str .=$row_billing_list['cancel_date'];
                }
                $str .='</td>';
                $str .='<td class="contact" '.$underline.'>';
                if($row_billing_list['dbn_id'] != ''){
                            $str .=$row_billing_list['rp_no'];
                }
                $str .='</td>';
                $str .='</tr>';
                
                $x_prj = $x_prj + 1;                
            }
            $str .='</table></tr>';

            //$str .='</table></tr>';                          
        
             $str .='<tr><table class="contacts" cellspacing="0" summary="Contacts template"><tr>
                        <td class="contactDept" colspan="10">Project Information</td>
                        </tr>';
            $str .='<tr>
                            <td class="contact" width="30px"><b>No</b></td>
                            <td class="contact"><b>Project No</b></td>
                            <td class="contact"><b>Time Charge</b></td>
                            <td class="contact"><b>Discount Rate (Actual)</b></td>
                            <td class="contact"><b>Discount Rate</b></td>
                            <td class="contact" width="100px"><b>Start Date</b></td>
                            <td class="contact" width="100px"><b>End Date</b></td>
                            <td class="contact"><b>Fee</b></td>
                            <td class="contact"><b>Billing Amount</b></td>
                            <td class="contact" width="50px"><b>Status</b></td>
                        </tr>';
                         $contract = $dao->get_where('contract', array('contract_id'=>$contract_id,'delete_status'=>'n'));
                         $sql_prj_list = "select * from contract where con_id = '".$contract_id."' and delete_status = 'n' order by start_date asc";
                         $query_prj_list = mysql_query($sql_prj_list);
                         $i=0;$a=0;
                         while($row_prj_list = mysql_fetch_assoc($query_prj_list)){
                            $i++;
                            $a++;
                            $timecharge = _getTimeCharge($row_prj_list['contract_id']);
                            $discount_actual = _discountRateActual($timecharge, $row_prj_list['monthly_free']);
                            $str .='<tr>
                                        <td class="contact">'.$a.'</td>
                                        <td class="contact">'.$row_prj_list['contract_no'].'</td>';
                            $str .='<td class="contact">';
                                        
                            $str .= $timecharge;
                                   
                            $str .='</td>';
                                        
                            $str .='<td class="contact">'.$discount_actual.'</td>
                                    <td class="contact">'.$row_prj_list['discount_rate'].'</td>
                                    <td class="contact" width="100px">'.$row_prj_list['start_date'].'</td>
                                    <td class="contact" width="100px">'.$row_prj_list['end_date'].'</td>
                                    <td class="contact">'.$row_prj_list['monthly_free'].'</td>
                                    <td class="contact">';
                            $sql_billing_amount = "select sum(bil_amount) as sum from billings left join tbl_debit_note on (bil_debitnote_id = dbn_id)
                                        where bil_contract_id = '".$contract_id."' and bil_status <> 'update' and bil_status <> 'cancel'
                                        and bil_date_plan >= '".$row_prj_list['start_date']."'
                                        and bil_date_plan <= '".$row_prj_list['end_date']."'
                                        and bil_delete_status = 'n'
                                        order by bil_date_plan";
                            $query_billing_amount = mysql_query($sql_billing_amount);
                            $row_billing_amount = mysql_fetch_assoc($query_billing_amount);
                            $str .=$row_billing_amount['sum'];
                            $str .='</td>';
                            $str .='<td class="contact" width="50px">';
                            if($row_prj_list['contract_status']=='Ended'){
                                            $str .="Ended";
                                        }
                                        else{
                                            if($row_prj_list['project_status'] == 'no'){
                                                $str .="Future";
                                            }
                                            else{
                                                $str .="Open";
                                            }
                                        }
                            $str .='</td>';
                            $str .='</tr>';
                            
                         }
                         $str .='</table></tr>';
            //$str .='</table></tr>'; 
            
        }
        //****************Hop dong One time**********************************//
        if($row['job_type']=='One time' || ($row['job_type']=='cs' && $row['cs_type']=='project')){
             $str .='<tr><table class="contacts" cellspacing="0" summary="Contacts template" ><tr>
                        <td class="contactDept" colspan="7">Billing Information</td>
                        </tr>';
             $str .='<tr>
                            <td class="contact" width="30px"><b>No</b></td>
                            <td class="contact"><b>Description</b></td>
                            <td class="contact"><b>Amount</b></td>
                            <td class="contact" width="150px"><b>Billing Date (Plan)</b></td>
                            <td class="contact" width="150px"><b>Billing Date (Actual)</b></td>
                            <td class="contact" width="150px"><b>Cancel Date</b></td>
                            <td class="contact"><b>Debit Note</b></td>
                            
                        </tr>';
            $sql_billing = "Select * from billings where bil_contract_id = '".$contract_id."' and bil_status <> 'update' and bil_delete_status = 'n' order by bil_phase_id ASC";
            $query_billing = mysql_query($sql_billing);
            $x = 1;
            $billing_total = 0;
            while($row_billing = mysql_fetch_assoc($query_billing)){
                
                unset($debit_note);
                $style_all = "style='text-decoration: none;'";
                if($row_billing['bil_status']=='cancel'){
                    $underline = 'style="text-decoration: line-through;color:red;"';
                    $cancel = 'style="text-decoration: none;"';
                }else{
                    $underline = '';
                    $cancel = '';
                }
                if($row_billing['bil_debitnote_id']!='0'){
                    $debit_note = $dao->get_where('tbl_debit_note',array('dbn_id'=>$row_billing['bil_debitnote_id']));
                }
                $str .='<tr >
                            <td class="contact" width="30px" '.$underline.'>'.$x.'</td>
                            <td class="contact" '.$underline.'>'.$row_billing['bil_description'].'</td>
                            <td class="contact" '.$underline.'>'.$row_billing['bil_amount'].'</td>
                            <td class="contact" width="150px" '.$underline.'>'.$row_billing['bil_date_plan'].'</td>
                            <td class="contact" width="150px" '.$underline.'>';
                if($row_billing['bil_date_actual'] != '0000-00-00'){
                    $str .=$row_billing['bil_date_actual'];
                }
                else{
                    $str .='&nbsp;';
                }
                $str .='</td>';
                $str .='<td class="contact" width="150px" '.$cancel.'>';
                if(isset($debit_note) && $debit_note[0]->cancel_date != '0000-00-00'){
                    $str .=$debit_note[0]->cancel_date;
                }else{
                    $str .='&nbsp;';
                }
                $str .='</td>';
                $str .='<td class="contact" '.$underline.'>';
                if(isset($debit_note)){
                    $str .=$debit_note[0]->rp_no;
                }
                else{
                    $str .='&nbsp;';                    
                }
                $str .='</td>';
                
                $str .='</tr>';
                
                $x = $x + 1;
            }
            $str .='</table></tr>';
           // $str .='</table></tr>';
        }
        $str .='<tr><table class="contacts" cellspacing="0" summary="Contacts template"><tr>
                    <td class="contactDept" colspan="2">Note</td>
                    </tr>';
        $str .='<tr>
                    <td class="contact" style="width:100px;"><b>Date</b></td>
                    <td class="contact"><b>Content</b></td>
                </tr>';
        $sql_get_note = "select * from contract_note where cn_contract_id = '".$contract_id."'";
        $query_get_note = mysql_query($sql_get_note);
        while($row_get_note = mysql_fetch_assoc($query_get_note)){
        
        $str .='<tr>
                    <td class="contact" style="width:100px;">'.$row_get_note['cn_register_date'].'</td>
                    <td class="contact">'.$row_get_note['cn_note'].'</td>
                </tr>';
        }
        $str .='</table></tr>';
        $str .='</table></body></html>';
               
		return $str;

}
	
function all_mail_for_client($id)
{
	//khoi tao xau rong
	$str = "";
	//lay du lieu
	$Client_id=$id;
	$sql="select * from client_list where Client_id='".$Client_id."'";
	$result=mysql_query($sql) or die(err_msg());
	$row=mysql_fetch_array(mysql_query($sql));
	if(mysql_num_rows($result)>0)
	{
		$C_ID=$row["C_ID"];
		$branch_id=$row["branch_id"];
		$Client_name_in_English=$row["Client_name_in_English"];
		$abbre_name = $row["abbre_name"];
		$Client_name_in_Vietnamese=$row["Client_name_in_Vietnamese"];
		$Company_Address_English=$row["Company_Address_English"];
		$Company_Address_Vietnamese=$row["Company_Address_Vietnamese"];
		$Address_for_mailing_English=$row["Address_for_mailing_English"];
		$Address_for_mailing_Vietnamese=$row["Address_for_mailing_Vietnamese"];
		//$status=$row['status'];
		$Type_of_investment=$row["Type_of_investment"];
		$Type_of_business=$row["Type_of_business"];
		$Industrial_park_ID=$row["Industrial_park_ID"];
		$Fiscal_year=$row["Fiscal_year"];
		$Tax_code=$row["Tax_code"];
		$auditor=$row["auditor"];
		$business_line=$row["business_line"];
		$Contract_person=$row["Contract_person"];
		$Telephone_1=$row["Telephone_1"];
		$Telephone_2=$row["Telephone_2"];
		$E_mail=$row["E_mail"];
		$Fax=$row["Fax"];
		$Contact_person=$row["Contact_person"];
		$Contact_telephone_1=$row["Contact_telephone_1"];
		$Contact_telephone_2=$row["Contact_telephone_2"];
		$Contact_E_mail=$row["Contact_E_mail"];
		$Contact_Fax=$row["Contact_Fax"];
		$pic1=$row["pic1"];
		$pic2=$row["pic2"];
	}
	//use DAO
	$dao = new DAO();
	//get  more information in related tables
	//$status_arr = array('1'=>'Normal','2'=>'Prospective');
	$temp_basic_info = $dao->get_row('client_basic_info',array('client_id'=>$Client_id,'delete_status'=>'n'));
	$temp_public_arr = array('1'=>'Yes,at','11'=>'1<sup>st</sup> session','12'=>'2<sup>nd</sup> session','13'=>'Other ('.$temp_basic_info->public_other_txt.')','2'=>'No');
	$size_arr = array('1'=>'Big','2'=>'SMBs');
	$bank_arr = array('11'=>'Mizuho','12'=>'BTMU','13'=>'SMBC','14'=>$temp_basic_info->i_bank_other);
	$temp_introduced_by_arr = array('1'=>$bank_arr[$temp_basic_info->i_bank].' Bank','2'=>'SCS Network','3'=>'From website','4'=>'Existing client ('.$temp_basic_info->i_existing_client.')','5'=>'Other ('.$temp_basic_info->introduced_by_other.')');
	$temp_head_quarter_arr = array('1'=>'Japan','2'=>'Singapore','3'=>'Hong Kong','4'=>'Other ('.$temp_basic_info->head_quater_other.')');
	//temporary foreign base
	$temp_foreign_base = $dao->get_row('client_foreign_base',array('client_id'=>$Client_id,'delete_status'=>'n'));
	$temp_relationship = $dao->get_row('client_relationship',array('client_id'=>$Client_id,'delete_status'=>'n'));
	$temp_foreign_base_arr = array();
	
	if($temp_foreign_base->japan==1){
		array_push($temp_foreign_base_arr,"Japan");
	}
	
	if($temp_foreign_base->singapore==1){
		array_push($temp_foreign_base_arr,"Singapore");
	}
	
	if($temp_foreign_base->hongkong==1){
		array_push($temp_foreign_base_arr,"Hong Kong");
	}
	
	if($temp_foreign_base->china==1){
		array_push($temp_foreign_base_arr,"China");
	}
	
	if($temp_foreign_base->thailand==1){
		array_push($temp_foreign_base_arr,"Thailand");
	}
	if($temp_foreign_base->indo==1){
		array_push($temp_foreign_base_arr,"Indo");
	}
	if($temp_foreign_base->other==1){
		array_push($temp_foreign_base_arr,"Other (".$temp_foreign_base->other_txt.")");
	}
	if($temp_foreign_base->no==1){
		$temp_foreign_base_arr = array();
		array_push($temp_foreign_base_arr,"No");
	}
	
	$temp_relationship_arr = array();
	
	if($temp_relationship->japan==1){
		array_push($temp_relationship_arr,"Japan");
	}
	
	if($temp_relationship->singapore==1){
		array_push($temp_relationship_arr,"Singapore");
	}
	
	if($temp_relationship->hongkong==1){
		array_push($temp_relationship_arr,"Hong Kong");
	}
	
	if($temp_relationship->china==1){
		array_push($temp_relationship_arr,"China");
	}
	if($temp_relationship->thailand==1){
		array_push($temp_relationship_arr,"Thailand");
	}
	if($temp_relationship->indo==1){
		array_push($temp_relationship_arr,"Indo");
	}
	
	if($temp_relationship->other==1){		
		array_push($temp_relationship_arr,"Other (".$temp_relationship->other_txt.")");
	}
	
	if($temp_relationship->no==1){
		$temp_relationship_arr = array();
		array_push($temp_relationship_arr,"No");
	}
	
	//$status_str = $status_arr[$status];
	$public = $temp_public_arr[$temp_basic_info->public];
	$size = $size_arr[$temp_basic_info->size];
	$introduced_by = $temp_introduced_by_arr[$temp_basic_info->introduced_by];
	$head_quarter = $temp_head_quarter_arr[$temp_basic_info->head_quarter];
	$foreign_base = implode(',',$temp_foreign_base_arr);
	$relationship = implode(',',$temp_relationship_arr);
	//***********************************************************************************/
	// -Branch
	$sql= "SELECT * from branch where (branch_id='".$branch_id."')and(delete_status='n')"; 		
	$rs= mysql_query($sql);		
	$row=mysql_fetch_array($rs);
    // -Industrial park Name
	$sql="SELECT * from industrial_park where delete_status='n' and industrial_park_id='".$Industrial_park_ID."'";  
	$rs= mysql_query($sql);	print mysql_error();
	$row1= mysql_fetch_array($rs);
    $str .= '
    <html>
    <head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<meta name="author" content="luyenlv" />
    <style>
    table.contacts
    { width: 680px;
    background-color: #fafafa;
    border: 1px #6699CC solid;
    border-collapse: collapse;
    border-spacing: 0px; }
    
    td.contactDept
    { background-color: #99CCCC;
    border: 1px #6699CC solid;
    font-family: Verdana;
    font-weight: bold;
    font-size: 12px;
    color: #404040; }
    
    td.contact
    { border: 1px #6699CC dotted;
    text-align: left;
    font-family: Verdana, sans-serif, Arial;
    font-weight: normal;
    font-size: .7em;
    color: #404040;
    background-color: #fafafa;
    padding-top: 4px;
    padding-bottom: 4px;
    padding-left: 8px;
    padding-right: 0px; }
    </style>
    </head>
    <body>
    <table class="contacts" cellspacing="0" summary="Contacts template">
    <tr>
    <td class="contactDept" colspan="2">Client Information</td>
    </tr>
    <tr>
    <td class="contact" width="35%">Branch:</td>
    <td class="contact" width="64%">'.$row["short_name"].'</td>
    </tr>
    <tr>
    <td class="contact" width="35%">Client ID:</td>
    <td class="contact" width="64%">'.$C_ID.'</td>
    </tr>
    <tr>
    <td class="contact" width="35%">Client name (English):</td>
    <td class="contact" width="64%">'.$Client_name_in_English.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Abbreviation name:</td>
    <td class="contact" width="64%">'.$abbre_name.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Client name (Vietnamese):</td>
    <td class="contact" width="64%">'.$Client_name_in_Vietnamese.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Company Address (English):</td>
    <td class="contact" width="64%">'.$Company_Address_English.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Company Address (Vietnamese):</td>
    <td class="contact" width="64%">'.$Company_Address_Vietnamese.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Address for mailing (English):</td>
    <td class="contact" width="64%">'.$Address_for_mailing_English.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Address for mailing (Vietnamese):</td>
    <td class="contact" width="64%">'.$Address_for_mailing_Vietnamese.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Type of investment:</td>
    <td class="contact" width="64%">'.$Type_of_investment.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Type of business:</td>
    <td class="contact" width="64%">'.$Type_of_business.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Business line:</td>
    <td class="contact" width="64%">'.$business_line.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Industrial park Name:</td>
    <td class="contact" width="64%">'.$row1["Industrial_park_Name"].'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Fiscal year end at:</td>
    <td class="contact" width="64%">'.$Fiscal_year.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Tax code:</td>
    <td class="contact" width="64%">'.$Tax_code.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Auditor:</td>
    <td class="contact" width="64%">'.$auditor.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Introduce by:</td>
    <td class="contact" width="64%">'.$introduced_by.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Head quarter:</td>
    <td class="contact" width="64%">'.$head_quarter.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Public:</td>
    <td class="contact" width="64%">'.$public.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Size:</td>
    <td class="contact" width="64%">'.$size.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Other foreign base:</td>
    <td class="contact" width="64%">'.$foreign_base.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Existing SCS relationship:</td>
    <td class="contact" width="64%">'.$relationship.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Representative person (Contract person):</td>
    <td class="contact" width="64%">'.$Contract_person.'</td>    
    </tr>
    <tr>    
    <td class="contact" width="35%">Telephone 01:</td>
    <td class="contact" width="64%">'.$Telephone_1.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Telephone 02:</td>
    <td class="contact" width="64%">'.$Telephone_2.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Email:</td>
    <td class="contact" width="64%">'.$E_mail.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Fax:</td>
    <td class="contact" width="64%">'.$Fax.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Contact person:</td>
    <td class="contact" width="64%">'.$Contact_person.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Contact telephone 1:</td>
    <td class="contact" width="64%">'.$Contact_telephone_1.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Contact telephone 2:</td>
    <td class="contact" width="64%">'.$Contact_telephone_2.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Contact E-mail:</td>
    <td class="contact" width="64%">'.$Contact_E_mail.'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">Contact Fax:</td>
    <td class="contact" width="64%">'.$Contact_Fax.'</td>
    </tr>
    <tr>
    <td class="contact" width="35%">PIC 1:</td>
    <td class="contact" width="64%">'.show_user_incharge($pic1).'</td>    
    </tr>
    <tr>
    <td class="contact" width="35%">PIC 2:</td>
    <td class="contact" width="64%">'.show_user_incharge($pic2).'</td>    
    </tr>
    </table>    
    </body>
    </html>
    ';
	return $str;
}

function all_mail_for_proposal($id)
{
	$str = "";
	$proposal_id = $id;
	$sql = "SELECT cl.Client_name_in_English, cp.short_name, cp.contract_type_Name, qfs.qfs_id, qfs.qfs_budget_calculate_status, qfs.qfs_service_fee, qfs.qfs_discount_fee, qfs.qfs_vat_fee, qfs.qfs_starting_date, qfs.qfs_next_update
			FROM quotation_for_service qfs
			INNER JOIN client_list cl ON ( qfs.qfs_client_id = cl.Client_id )
			INNER JOIN contract_type cp ON ( qfs.qfs_service_id = cp.contract_type_id )
			WHERE qfs.qfs_id = '".$proposal_id."'";
	$query = mysql_query($sql);
	$row = mysql_fetch_assoc($query);
    $str .= '
    <html>
    <head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<meta name="author" content="luyenlv" />
    <style>
    table.contacts
    { width: 680px;
    background-color: #fafafa;
    border: 1px #6699CC solid;
    border-collapse: collapse;
    border-spacing: 0px; }
    
    td.contactDept
    { background-color: #99CCCC;
    border: 1px #6699CC solid;
    font-family: Verdana;
    font-weight: bold;
    font-size: 12px;
    color: #404040; }
    
    td.contact
    { border: 1px #6699CC dotted;
    text-align: left;
    font-family: Verdana, sans-serif, Arial;
    font-weight: normal;
    font-size: .7em;
    color: #404040;
    background-color: #fafafa;
    padding-top: 4px;
    padding-bottom: 4px;
    padding-left: 8px;
    padding-right: 0px; }
    </style>
    </head>
    <body>
    <table class="contacts" cellspacing="0" summary="Contacts template">
    <tr>
    <td class="contactDept" colspan="2">Proposal Information</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Client:</td>
    <td class="contact" width="69%">'.$row['Client_name_in_English'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Service:</td>
    <td class="contact" width="69%">'.$row['short_name']." :: ".$row['contract_type_Name'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Time budget calculation:</td>
    <td class="contact" width="69%">'.ucwords($row['qfs_budget_calculate_status']).'</td>
    </tr>
    ';
    
	if($row['qfs_budget_calculate_status'] == 'yes'){
        $str .='<tr>
    <td class="contact" width="30%">Time budget calculation detail:</td>
    <td class="contact" width="69%">';
		$sql_tbd = "SELECT 	jmr_working_content as cv , sum(jmr_working_time) as time
						FROM job_member_relation WHERE jmr_qfs_id ='".$row['qfs_id']."'
						Group by jmr_working_content";
		$query_tbd = mysql_query($sql_tbd);
		while($row_tbd = mysql_fetch_assoc($query_tbd)){
			$str .= "+ ".$row_tbd['cv']." (".$row_tbd['time']." h)<br>";
		}
        $str .= '</td></tr>';
	}
    $str .= '<tr>
    <td class="contact" width="30%">Service Fee:</td>
    <td class="contact" width="69%">'.$row['qfs_service_fee'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Discount:</td>
    <td class="contact" width="69%">'.$row['qfs_discount_fee'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">VAT:</td>
    <td class="contact" width="69%">'.$row['qfs_vat_fee'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Propose date:</td>
    <td class="contact" width="69%">'.$row['qfs_starting_date'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Next update date:</td>
    <td class="contact" width="69%">'.$row['qfs_next_update'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Note:</td>
    <td class="contact" width="69%">
    ';
	$sql_note = "select * from prospective_service_note where psn_qsf_id = '".$proposal_id."' order by psn_register_date asc";
	$query_note = mysql_query($sql_note);
	while($row_note = mysql_fetch_assoc($query_note)){
		$str .= $row_note['psn_register_date'].": ".$row_note['psn_note']."<br>";
	}
    $str .='</td></tr>';
    $str .='</table></body></html>';
	return $str;
}
function all_mail_for_opc($id)
{
	$str = '';
	$opc_id = $id;
	$sql = "SELECT oc.opc_form_no,oc.opc_date, oc.opc_type, oc.opc_subtype, oc.opc_voucher_number, oc.opc_amount, oc.opc_description, ct1.contract_no AS CN1, ct2.contract_no AS CN2, cl1.Client_name_in_English  AS Client_1, cl2.Client_name_in_English  AS Client_2, cp.short_name AS CP_TYPE, cp.contract_type_Name, br.short_name AS BR
	FROM opc_contracts oc
	INNER JOIN contract ct1 ON ct1.contract_id = oc.opc_contract_id
	INNER JOIN client_list cl1 ON ct1.client_id = cl1.Client_id
	LEFT JOIN client_list cl2 ON ct1.client_id_2 = cl2.Client_id
	INNER JOIN contract_type cp ON ct1.contract_type_id = cp.contract_type_id
	INNER JOIN branch br ON ct1.branch_id = br.branch_id
	LEFT JOIN contract ct2 ON oc.opc_project_id = ct2.contract_id
	WHERE opc_id = '".$opc_id."'
	";
	$query = mysql_query($sql);
	$row = mysql_fetch_array($query);
    $str .='
    <html>
    <head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<meta name="author" content="luyenlv" />
    <style>
    table.contacts
    { width: 680px;
    background-color: #fafafa;
    border: 1px #6699CC solid;
    border-collapse: collapse;
    border-spacing: 0px; }
    
    td.contactDept
    { background-color: #99CCCC;
    border: 1px #6699CC solid;
    font-family: Verdana;
    font-weight: bold;
    font-size: 12px;
    color: #404040; }
    
    td.contact
    { border: 1px #6699CC dotted;
    text-align: left;
    font-family: Verdana, sans-serif, Arial;
    font-weight: normal;
    font-size: .7em;
    color: #404040;
    background-color: #fafafa;
    padding-top: 4px;
    padding-bottom: 4px;
    padding-left: 8px;
    padding-right: 0px; }
    </style>
    </head>
    <body>
    <table class="contacts" cellspacing="0" summary="Contacts template">
    <tr>
    <td class="contactDept" colspan="2">OPEs Information</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Form No.:</td>
    <td class="contact" width="69%">'.$row['opc_form_no'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Date:</td>
    <td class="contact" width="69%">'.$row['opc_date'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Branch:</td>
    <td class="contact" width="69%">'.$row['BR'].'</td>
    </tr>
    ';
	if ($row["Client_2"] !="") 
	{
    	$str .= '
        <tr>
        <td class="contact" width="30%">Client 1:</td>
        <td class="contact" width="69%">'.$row['Client_1'].'</td>
        </tr>
        <tr>
        <td class="contact" width="30%">Client 2:</td>
        <td class="contact" width="69%">'.$row['Client_2'].'</td>
        </tr>
        ';
	}
	else 
	{
		$str .= '
        <tr>
        <td class="contact" width="30%">Client 1:</td>
        <td class="contact" width="69%">'.$row['Client_1'].'</td>
        </tr>';
	}	
	
	$str .= '<tr>
    <td class="contact" width="30%">Contract Type:</td>
    <td class="contact" width="69%">'.$row['contract_type_Name'].'</td>
    </tr>';
	if($row['CN2']!='')
	{
		$str .=	'<tr>
    <td class="contact" width="30%">Contract/Project No.:</td>
    <td class="contact" width="69%">'.$row['CN2'].'</td>
    </tr>';
	}
	else
	{
		$str .= '<tr>
    <td class="contact" width="30%">Contract/Project No.:</td>
    <td class="contact" width="69%">'.$row['CN1'].'</td>
    </tr>';
	}
    $str .= '<tr>
    <td class="contact" width="30%">Type:</td>
    <td class="contact" width="69%">'.$row['opc_type'].' : '.$row['opc_subtype'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Voucher number:</td>
    <td class="contact" width="69%">'.$row['opc_voucher_number'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Amount:</td>
    <td class="contact" width="69%">'.number_format($row['opc_amount']).'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Description:</td>
    <td class="contact" width="69%">'.$row['opc_description'].'</td>
    </tr>';
    $str .='</table></body></html>';
	return $str;
}

function show_internal_types($id)  
{
	$sql = "SELECT siw_sub_code, siw_sub_description FROM sub_internal_work  where siw_id = '".$id."' and siw_delete_status='n'";
	$query = mysql_query($sql);
	$rows = mysql_fetch_array($query);
	if(mysql_num_rows($query)>0)
	{
		return $rows['siw_sub_code'].'::'.$rows['siw_sub_description'];
	}
}

function all_mail_for_opi($id)
{
	$opi_id = $id;
	$str ='';
	$sql = "select * from opi_internal_tasks where  opi_id = ".$opi_id." and opi_delete_status='n' ";
	$query = mysql_query($sql);
	if(mysql_num_rows($query)>0)
	{
		$rows = mysql_fetch_array($query);
    $str .='
    <html>
    <head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<meta name="author" content="luyenlv" />
    <style>
    table.contacts
    { width: 680px;
    background-color: #fafafa;
    border: 1px #6699CC solid;
    border-collapse: collapse;
    border-spacing: 0px; }
    
    td.contactDept
    { background-color: #99CCCC;
    border: 1px #6699CC solid;
    font-family: Verdana;
    font-weight: bold;
    font-size: 12px;
    color: #404040; }
    
    td.contact
    { border: 1px #6699CC dotted;
    text-align: left;
    font-family: Verdana, sans-serif, Arial;
    font-weight: normal;
    font-size: .7em;
    color: #404040;
    background-color: #fafafa;
    padding-top: 4px;
    padding-bottom: 4px;
    padding-left: 8px;
    padding-right: 0px; }
    </style>
    </head>
    <body>
    <table class="contacts" cellspacing="0" summary="Contacts template">
    <tr>
    <td class="contactDept" colspan="2">OPEs Information</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Form No:</td>
    <td class="contact" width="69%">'.$rows['opi_form_no'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Date:</td>
    <td class="contact" width="69%">'.$rows['opi_date'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Branch:</td>
    <td class="contact" width="69%">'.show_name_branch($rows['opi_branch_id']).'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Internal task:</td>
    <td class="contact" width="69%">'.show_internal_types($rows['opi_siw_id']).'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Type:</td>
    <td class="contact" width="69%">'.$rows['opi_type'].' : '.$rows['opi_subtype'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Voucher number:</td>
    <td class="contact" width="69%">'.$rows['opi_voucher_number'].'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Amount:</td>
    <td class="contact" width="69%">'.number_format($rows['opi_amount']).'</td>
    </tr>
    <tr>
    <td class="contact" width="30%">Description:</td>
    <td class="contact" width="69%">'.$rows['opi_description'].'</td>
    </tr>
    </table>    
    </body>
    </html>
    ';
	}
	return $str;
}
//End
#---------------#
function check_grant_c_id($client)
{
	//Delete and cl.app_status='approval';
	$sql = "select * from client_list cl 
	inner join client_basic_info cbi on cl.Client_id=cbi.client_id 
	inner join client_foreign_base cfb on cl.Client_id = cfb.client_id 
	inner join client_relationship cr on cl.Client_id = cr.client_id 
	where cl.branch_id <> '' 
	and cl.app_status='approval'
	 and cl.Client_name_in_English <> '' and cl.Company_Address_English <> '' 
	 and cl.Address_for_mailing_English <> '' 
	 and cl.Type_of_investment <> '' 
	 and cl.Contract_person <>'' 
	 and cl.Telephone_1 <>'' 
	 and cl.E_mail <>'' 
	 and cl.Contact_telephone_1 <> '' 
	 and cl.Type_of_business!='' and cl.business_line <> '' and cl.Contact_person <> '' and cl.pic1 <> '0'  and cl.delete_status='n' and (cr.japan <> '0' OR cr.singapore <> '0' OR cr.hongkong <> '0' OR cr.china <> '0' OR cr.thailand <> '0' OR cr.indo <> '0' OR cr.other <> '0' OR cr.no <> '0') and (cfb.japan <> '0' OR cfb.singapore <> '0' OR cfb.hongkong <> '0' OR cfb.china <> '0' OR cfb.thailand <> '0' OR cfb.indo <> '0' OR cfb.other <> '0' OR cfb.no <> '0') and (cbi.public <> 0 or cbi.public_other_txt <>'') and cbi.size <> 0 and ( cbi.introduced_by <> 0 or cbi.introduced_by_other <> '') and (cbi.head_quarter <> 0 or cbi.head_quater_other <>'') and cl.Client_id=".$client;
	
	$query = mysql_query($sql);
	if(mysql_num_rows($query)>0)
	{
		$rows_query = mysql_fetch_array($query);
		$row_branch = $rows_query['branch_id'];
		
		$sql_branch = "	SELECT MAX(C_ID) AS id_max_branch FROM client_list WHERE delete_status='n' AND branch_id='".$row_branch ."' ORDER BY C_ID DESC ";
		$qr_br = mysql_query($sql_branch);
		$rows_max = mysql_fetch_array($qr_br);
		return $rows_max['id_max_branch'] + 1 ;
	}
	else
	{
		return 0;
	}
	
}

function check_approval($table,$where_id,$id,$status_app,$more)
{
	$sql = "select app_status from ".$table." where ".$where_id." = ".$id." and ".$status_app." = 'n' and app_status ='approval' ";
	if($more!=""){
		$sql .= $more;
	}
	else{
		$sql .= "";
	}
	$query = mysql_query($sql);
	if(mysql_num_rows($query)>0){
	 	return true;	
	}
	else{
		return false;
	}
		
}

//Use for update when rejected
//Begin
function update_approval_rfa_contract($tables,$where,$id,$more_where,$mail_cc,$title_name,$more_name,$mail_contents)
{
	$sql = "update ".$tables." set app_status = 'approval', app_status_edit = 'approval' where ".$where." = ".$id;
	if($more_where!=""){
		$sql .= $more_where;
	}
	else{
		$sql .="";
	}
	mysql_query($sql);
    //Viec send mail se phai dua hoan toan vao contract setting khong lien quan toi viec gui mail rieng re
    /*
	$subject_approval = "CONTRACT::".$title_name."::".$more_name;
	$content_approval = 'Please confirm the below information. If you find any error please contact IC Div for further help.<br /><br />';
	$tags = 'p';
    $mail_contents_new = strip_only($mail_contents, $tags, true);
    $content_approval .= $mail_contents_new;
	sendMail($mail_cc,$subject_approval,$content_approval);
    */
}

function update_approval_rfa_client($tables,$where,$id,$more_where,$mail_cc,$title_name,$more_name,$mail_contents)
{
	$sql = "update ".$tables." set app_status = 'approval', app_status_edit = 'approval' where ".$where." = ".$id;
	if($more_where!=""){
		$sql .= $more_where;
	}
	else{
		$sql .="";
	}
    mysql_query($sql);    
    $sql_select = "select C_ID from ".$tables." where ".$where." = ".$id;
    $query_select = mysql_query($sql_select);
    $row_select = mysql_fetch_array($query_select);
    if($row_select['C_ID']==0){        
	$sql_udate_cid = "update ".$tables." set C_ID =".check_grant_c_id($id)." where  ".$where." =".$id;
	mysql_query($sql_udate_cid);
    }    
	$subject_approval = "CLIENT::".($title_name);
	$content_approval = 'Please confirm the below information. If you find any error please contact IC Div for further help.<br /><br />';
	$tags = 'p';
    $mail_contents_new = strip_only(all_mail_for_client($id), $tags, true);
    $content_approval .= $mail_contents_new;
	sendMail($mail_cc,pregReplace($subject_approval),pregReplace($content_approval));
}

function update_approval_rfa_proposal($tables,$where,$id,$more_where,$mail_cc,$title_name,$more_name,$mail_contents)
{
	$sql = "update ".$tables." set app_status = 'approval', app_status_edit = 'approval' where ".$where." = ".$id;
	if($more_where!=""){
		$sql .= $more_where;
	}
	else{
		$sql .="";
	}
	mysql_query($sql);
	$subject_approval = "PROPOSAL::".($title_name)."::".$more_name;
	$content_approval = 'Please confirm the below information. If you find any error please contact IC Div for further help.<br /><br />';
	$tags = 'p';
    $mail_contents_new = strip_only($mail_contents, $tags, true);
    $content_approval .= $mail_contents_new;
	sendMail($cc_to_group_person,pregReplace($subject_approval),pregReplace($content_approval), FULL_MAIL);
}

function name_client($id)
{
	$sql = 'select Client_name_in_English from client_list where Client_id='.$id.' and app_status="approval" and delete_status="n" ';
	$query = mysql_query($sql);
	$rows = mysql_fetch_array($query);
	return $rows['Client_name_in_English'];
}

function title_client_mail($id)
{
	$sql = "SELECT *
			FROM opc_contracts opc
			INNER JOIN contract ct ON opc.opc_contract_id = ct.contract_id
			INNER JOIN contract_type ct_type ON ct_type.contract_type_id = ct.contract_type_id
			WHERE opc.opc_id='".$id."' and opc.opc_delete_status='n' ";
	$query = mysql_query($sql);
	if(mysql_num_rows($query)>0){
		$rows = mysql_fetch_array($query);
	if($rows["client_id_2"]!=0){
		return name_client($rows["client_id_2"]).'(on collaboration with '.name_client($rows["client_id"]).')';
	}
	else{
		return name_client($rows["client_id"]);
	}
	}
}

function show_service_contract_types($id)
{
	$sql = "select * from contract_type where contract_type_id= '".$id."' and delete_status='n'";
	$query = mysql_query($sql);
	$rows = mysql_fetch_array($query);
	if(mysql_num_rows($query)>0){
		return $rows['short_name'];
	}
}

function internal_type($id)  
{
	$sql = "SELECT siw_sub_code, siw_sub_description FROM sub_internal_work  where siw_id = '".$id."' and siw_delete_status='n'";
	$query = mysql_query($sql);
	$rows = mysql_fetch_array($query);
	if(mysql_num_rows($query)>0){
		return $rows['siw_sub_code'].'::'.$rows['siw_sub_description'];
	}
}

function update_approval_rfa_opc($tables,$where,$id,$more_where,$mail_cc,$title_name,$mail_contents)
{
	$sql = "update ".$tables." set app_status = 'approval' where ".$where." = ".$id;
	if($more_where!=""){
		$sql .= $more_where;
	}
	else{
		$sql .="";
	}
	mysql_query($sql);
	//Note $title_name = id of contract_type;
	$subject_approval = "OPE::".title_client_mail($id).'::'.(show_service_contract_types($title_name));
	$content_approval = 'Please confirm the below information. If you find any error please contact IC Div for further help.<br /><br />';
	$tags = 'p';
    $mail_contents_new = strip_only($mail_contents, $tags, true);
    $content_approval .= $mail_contents_new;
	sendMail($mail_cc,pregReplace($subject_approval),pregReplace($content_approval));
}

function update_approval_rfa_opi($tables,$where,$id,$more_where,$mail_cc,$title_name,$mail_contents)
{
	$sql = "update ".$tables." set app_status = 'approval' where ".$where." = ".$id;
	if($more_where!=""){
		$sql .= $more_where;
	}
	else{
		$sql .="";
	}
	mysql_query($sql);
	//$title_name = id of sub_internal_work;
	$subject_approval = "OPE::".internal_type($title_name);
	$content_approval = 'Please confirm the below information. If you find any error please contact IC Div for further help.<br /><br />';
	$tags = 'p';
    $mail_contents_new = strip_only($mail_contents, $tags, true);
    $content_approval .= $mail_contents_new;
	sendMail($mail_cc,pregReplace($subject_approval),pregReplace($content_approval));
}


function r_process_for_request($user,$group)
{
	//Lay lai ten cua user hoac group de luu vao bang request
	if($user!=0){
	$sql_get_name_user_t_all = mysql_fetch_array(mysql_query("select full_name from tbl_users where id_user='".$user."' and delete_status='n' "));
	return $sql_get_name_user_t_all['full_name'];
	}
	else{
	$sql_get_name_user_t_all = mysql_fetch_array(mysql_query("select g_name from groups where g_id='".$group."' and g_delete_status='n' "));
	return $sql_get_name_user_t_all['g_name'];
	}
}
function send_mail_for_group_or_user($user,$group,$subject,$mail_contents,$mail_cc)
{
    //Gui mail cho mot user hoac mot nhom user o lan duyet dau tien
    if($user!=0){
		$sql_mail_to_user_t_all = "select email from tbl_users where id_user='".$user."'  and delete_status='n' ";
		$sql_run_mail_to_user_t_all = mysql_query($sql_mail_to_user_t_all);
		if(mysql_num_rows($sql_run_mail_to_user_t_all)>0){
		$row_mail_user_t_all = mysql_fetch_array($sql_run_mail_to_user_t_all);
		$mail_user_all_t_all = $row_mail_user_t_all['email'];
		sendMail($mail_user_all_t_all,$subject,$mail_contents,$mail_cc);
		}
	}
	else{
		$sql_mail_to_user_t_all = "select g_email from groups where g_id = '".$group."' and g_delete_status='n'";
		$sql_run_mail_to_user_t_all = mysql_query($sql_mail_to_user_t_all);
		if(mysql_num_rows($sql_run_mail_to_user_t_all)>0){
		$row_mail_user_t_all = mysql_fetch_array($sql_run_mail_to_user_t_all);
		$mail_user_all_t_all = $row_mail_user_t_all['g_email'];
		sendMail($mail_user_all_t_all,pregReplace($subject),pregReplace($mail_contents),$mail_cc);
		}	
	}
}	
//End
#----------------#
####################################
//Check all query into transation
function begin_trans(){
@mysql_query("BEGIN");
}
function commit_trans(){
@mysql_query("COMMIT");
}
function rollback_trans(){
@mysql_query("ROLLBACK");
}
####################################
####################################
function someoneElseChangesAContract($id,$rfa_id){
    $sql_contract = "SELECT * FROM contract WHERE contract_id = '".$rfa_id."' and delete_status='n' ";
    $qr_contract = mysql_query($sql_contract);
    if(mysql_num_rows($qr_contract)>0){
        $row_contract = mysql_fetch_array($qr_contract);
        $ls_modify_id = $row_contract['ls_modify_id'];
        $def_modify_id = explode(',',$ls_modify_id);
        $count_ls_modify_id = count($def_modify_id);
        $count_list_last = $count_ls_modify_id - 1;
            if($id!=$def_modify_id[$count_list_last]){
                return true;
            }
            else{
                return false;
            }
    }else{
        return false;
    }  
}
function someoneElseChangesAContractCreatedByMe($id,$rfa_id){
    $sql_contract = "SELECT * FROM contract WHERE contract_id = '".$rfa_id."' and create_id='".$id."' and delete_status='n' ";
    $qr_contract = mysql_query($sql_contract);
    if(mysql_num_rows($qr_contract)>0){
        $row_contract = mysql_fetch_array($qr_contract);
        $ls_modify_id = $row_contract['ls_modify_id'];
        $def_modify_id = explode(',',$ls_modify_id);
        $count_ls_modify_id = count($def_modify_id);
        $count_list_last = $count_ls_modify_id - 1;
        if($count_ls_modify_id>=2){
            if($def_modify_id[$count_list_last]!="" && $id!=$def_modify_id[$count_list_last]){
                return true;
            }
            else{
                return false;
            }
        }else{
            return false;
        }
    }else{
        return false;
    }
}
function someoneElseChangesAContractLastModifiedByMe($id,$rfa_id){
    $sql_contract = "SELECT * FROM contract WHERE contract_id = '".$rfa_id."' and create_id='".$id."' and delete_status='n' ";
    $qr_contract = mysql_query($sql_contract);
    if(mysql_num_rows($qr_contract)>0){
        $row_contract = mysql_fetch_array($qr_contract);
        $ls_modify_id = $row_contract['ls_modify_id'];
        $def_modify_id = explode(',',$ls_modify_id);
        $count_ls_modify_id = count($def_modify_id);
        $count_list_last = $count_ls_modify_id - 1;
        $count_list_first = $count_list_last-1;
        if($count_ls_modify_id>=2){
            if($id==$def_modify_id[$count_list_first] && $id!=$def_modify_id[$count_list_last]){
                return true;
            }
            else{
                return false;
            }
        }else{
            return false;
        }
    }else{
        return false;
    }  
}
function someoneElseChangesAContractThatIAmATeamLeader($id,$rfa_id){
    $sql_contract = "SELECT * FROM contract WHERE contract_id = '".$rfa_id."' and scs_contract_per1='".$id."' and delete_status='n' ";
    $qr_contract = mysql_query($sql_contract);
    if(mysql_num_rows($qr_contract)>0){
        $row_contract = mysql_fetch_array($qr_contract);
        $ls_modify_id = $row_contract['ls_modify_id'];
        $contract_per1 = $row_contract['scs_contract_per1'];
        $def_modify_id = explode(',',$ls_modify_id);
        $count_ls_modify_id = count($def_modify_id);
        $count_list_last = $count_ls_modify_id - 1;
        if($count_ls_modify_id>=2){
            if($contract_per1!=$def_modify_id[$count_list_last]){
                return true;
            }
            else{
                return false;
            }
        }else{
            return false;
        }
    }else{
        return false;
    }  
}
function sendMailFromSettingContractAll($action,$rfa_id,$subject,$content){
    $sql = "SELECT DISTINCT (csa_user_id) FROM contract_setting_alerts WHERE csa_delete_status = 'n' and status='1' ORDER BY status DESC";
    $qr=mysql_query($sql);
    if(mysql_num_rows($qr)>0){
        $id_send = '0';
        while($row = mysql_fetch_array($qr)){
        //$action 2:tao, 3:sua, 4:close contract
        //gui mail khi co cac thay doi ve (them, xoa, sua, close contact)
        $sql_contract_setting_alerts = "SELECT * FROM contract_setting_alerts where csa_user_id ='".$row['csa_user_id']."' and csa_delete_status='n' and status='1' ORDER BY status DESC LIMIT 0, 1";
        $qr_contract_setting_alerts = mysql_query($sql_contract_setting_alerts);
            if(mysql_num_rows($qr_contract_setting_alerts)>0){
                $row_csa = mysql_fetch_array($qr_contract_setting_alerts);
                $change_type = $row_csa['csa_change_type']; 
                $send_alerts_when = $row_csa['csa_event_alert'];
                $check = false;
                //with change_type =1 include create modify and close
                if($change_type=="1"){
                    //action are create and change type =1
                    if($action=="2"){
                        if($send_alerts_when=="1"){
                            $check = true;
                        }
                    }
                    //end 
                    //action are create and close
                    if($action!="2"){
                        if($send_alerts_when=="1"){
                            $check = true;
                        }
                        //Neu khong thoa man ta phai xet rieng tung truong hop
                        if($send_alerts_when=="2"){
                            if(someoneElseChangesAContract($row['csa_user_id'],$rfa_id)){
                            $check = true;  
                            }
                        }
                        if($send_alerts_when=="3"){
                            if(someoneElseChangesAContractCreatedByMe($row['csa_user_id'],$rfa_id)){
                            $check = true;   
                            }
                        }
                        if($send_alerts_when=="4"){
                            if(someoneElseChangesAContractLastModifiedByMe($row['csa_user_id'],$rfa_id)){
                            $check = true;   
                            }
                        }
                        if($send_alerts_when=="5"){
                            if(someoneElseChangesAContractThatIAmATeamLeader($row['csa_user_id'],$rfa_id)){
                            $check = true;   
                            }
                        }
                    }
                }
                //with change_type!=1 include create modify and close
                else{
                    if($action==$change_type && $action=="2"){
                        if($send_alerts_when=="1"){
                            $check = true;
                        }
                    }
                    //action are create and close
                    if($action==$change_type && $action!="2"){
                        //true when send alert when =1
                        if($send_alerts_when=="1"){
                            $check = true;
                        }
                        //private when send alert !=1
                        if($send_alerts_when=="2"){
                            if(someoneElseChangesAContract($row['csa_user_id'],$rfa_id)){
                            $check = true;  
                            }
                        }
                        if($send_alerts_when=="3"){
                            if(someoneElseChangesAContractCreatedByMe($row['csa_user_id'],$rfa_id)){
                            $check = true;   
                            }
                        }
                        if($send_alerts_when=="4"){
                            if(someoneElseChangesAContractLastModifiedByMe($row['csa_user_id'],$rfa_id)){
                            $check = true;   
                            }
                        }
                        if($send_alerts_when=="5"){
                            if(someoneElseChangesAContractThatIAmATeamLeader($row['csa_user_id'],$rfa_id)){
                            $check = true;   
                            }
                        }
                    }
                    
                }
            }
            if($check){
                    $sqls = "select * from contract_setting_alerts where csa_user_id='".$row['csa_user_id']."' and status='1' and csa_delete_status='n' LIMIT 0 , 1 ";
                    $qrs = mysql_query($sqls);
                    if(mysql_num_rows($qrs)>0){
                        $rows = mysql_fetch_array($qrs);
                        
                        if($rows['csa_alert_when']=='1'){
                        $id_send .=','.$row['csa_user_id'];
                        }else{
                            //get mail of user                        
                            $sql_mail_save = "SELECT email FROM tbl_users where id_user ='".$row['csa_user_id']."' and delete_status='n' and status='Enable' ";
                            $qr_mail_save = mysql_query($sql_mail_save);
                            $row_mail_save = mysql_fetch_array($qr_mail_save);
                            $date = $rows['csa_date_alert'];
                            $time = $rows['csa_time_alert'];
                            //save mail of user                                                                        
                            $sql_insert_email_alert ="INSERT INTO contract_email_alert(cea_email_to, cea_subjects, cea_description, cea_status, date_send, time_send, cea_create_date, cea_time_sent) VALUES('".$row_mail_save['email']."', '".str_replace('Approval::','CONTRACT::',$subject)."', '".$content."', 'n','".$date."','".$time."', '".date('Y-m-d')."', '0000-00-00 00:00:00')";
                            mysql_query($sql_insert_email_alert);
                        }
                    }
            }
        }
        $sql_mail = "SELECT email FROM tbl_users where id_user in(".$id_send.") and delete_status='n' and status='Enable' ";
        $qr_mail = mysql_query($sql_mail);
        if(mysql_num_rows($qr_mail)>0){
        $email = '';
            while($row_mail = mysql_fetch_array($qr_mail)){
                $email .= $row_mail['email'].',';
            }
        $send_mail = substr($email,0,-1);
        $_subject = str_replace('Approval::','CONTRACT::',($subject));
        sendMail($send_mail,pregReplace($_subject),pregReplace($content));   
        }    
    }
}
function check_show_cl_with_three_contract($contract_id){
        $sql_get_client  = 'SELECT Client_name_in_English FROM client_list AS cl';
    	$sql_get_client .= ' INNER JOIN contract AS ct ON cl.Client_id=ct.client_id_2';
    	$sql_get_client .=	' WHERE contract_id='.$contract_id;
        $qr = mysql_query($sql_get_client);
        if(mysql_num_rows($qr)){
            $rc = mysql_fetch_array($qr);
            return $rc['Client_name_in_English'];
        }else{
            return '';
        }
}
//End send mail
####################################
//Insert total RFA
//Begin
function rfa_insert_total($workflow_id,$module_id,$rfa_id,$approval,$subject,$content,$mail_cc,$object_title,$date_timestamp,$more_inf,$app_status,$app_status_edit,$mail_contents)
{
    //update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
    //update_approval_rfa_contract($tables,$where,$id,$more_where,$mail_cc,$title_name,$more_name,$mail_contents)
	/* note:
	+ $approval: is waiting or approval
	+ $workflow_id: id cua wf
	+ $module_id: module can dua vao rfa
	+ $rfa_id: chinh la id cua cac object nhu: client,quotation,contract,opes...
	+ $subject: Chu de mail
	+ $content: Noi dung mail
	Vi du:
	- $subject = 'Approval::CLIENT::'.html_entity_decode($object_title);
	- $content="Please review and approve the below request as soon as possible.<br><br>";
	+ $mail_cc: La mail gui toi nguoi tao ra rfa
	+ $object_title: Tieu de cua cac email( vi du client_name,service_name)
	Cach dung
	- Neu la client
	Rfa_Insert_Total($workflow_id_client,$module_id_client,$client_id,$subject_client,$content_client,$mail_cc_client,$Client_name_in_English,$date_timestamp,'');
	- Neu la Service
	Rfa_Insert_Total($workflow_id_service,$module_id_service,$service_id,$subject_service,$content_service,$mail_cc_service,$Client_name_in_English,$date_timestamp,$service_name);
	*/
	//Lay validate neu co nhieu ban ghi de luu cac steps va xu ly cho viec so sanh
	//$app_status: check app_status of rfa
	//$app_status_edit: check app_status_edit of rfa
	if($app_status=="approval" && $app_status_edit=="approval"){
		$r_process = 'Edit';
		switch($module_id){
			case 6:
				$sql_update_rfa_for_edit = " UPDATE contract set app_status_edit='waiting' where contract_id='".$rfa_id."' and delete_status='n' ";
				mysql_query($sql_update_rfa_for_edit);
				print mysql_error();
				break;
			case 8:
				$sql_update_rfa_for_edit = " UPDATE client_list set app_status_edit='waiting' where Client_id='".$rfa_id."' and delete_status='n' ";
				mysql_query($sql_update_rfa_for_edit);
				print mysql_error();
				break;
			case 11:
				$sql_update_rfa_for_edit = " UPDATE quotation_for_service set app_status_edit='waiting' where qfs_id='".$rfa_id."' and qfs_delete_status='n' ";
				mysql_query($sql_update_rfa_for_edit);
				print mysql_error();
				break;
			case 14:
				$sql_update_rfa_for_edit = " UPDATE opc_contracts set app_status_edit='waiting' where opc_id='".$rfa_id."' and opc_delete_status='n' ";
				mysql_query($sql_update_rfa_for_edit);
				print mysql_error();
				break;
			case 15:
				$sql_update_rfa_for_edit = " UPDATE opi_internal_tasks set app_status_edit='waiting' where opi_id='".$rfa_id."' and opi_delete_status='n'";
				mysql_query($sql_update_rfa_for_edit);
				print mysql_error();
				break;
		}
	}
	else{
		$r_process = 'Create';
	}
    if($r_process=="Create"){
        $action_send = '2';
    }else{
        $action_send = '3';
    }
	$sql_valid = " SELECT wvd_id, wvd_valid_date as valid,wvd_wf_id	FROM workflow_valid_dates WHERE wvd_wf_id = '".$workflow_id."'	AND now() >= wvd_valid_date	AND wvd_delete_status = 'n'	ORDER BY wvd_valid_date DESC
	LIMIT 0 , 1 ";
	$run_valid = mysql_query($sql_valid);
	$row_valid =  mysql_fetch_array($run_valid);
	if(mysql_num_rows($run_valid)>0){
		$valid = $row_valid['valid'];
		$valid_m = $row_valid['wvd_id'];
		$valid_wf = $row_valid['wvd_wf_id'];
	}
	//Ket thuc viec lay validate	
	//Kiem tra buoc kiem soat
	$sql_all = "SELECT * FROM workflow_steps INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id	WHERE workflow_valid_dates.wvd_wf_id ='".$valid_wf."' and workflow_steps.wfs_delete_status = 'n' and wvd_valid_date = '".$valid."' HAVING wfs_control_status = 1 order by workflow_steps.wfs_id asc ";
	
	$run_mysql_all = mysql_query($sql_all);
    $control_status = false;
	if(mysql_num_rows($run_mysql_all)>0){
		$control_status = true;
	}
	//Ket thuc kiem tra buoc kiem soat
    //Kiem tra dieu kien voi quy trinh duyet co kiem soat
	if($control_status){
       	$string_step_temp_all = '';
        $string_step_t_all = '';
        $sosanh_user_t_all = array($_COOKIE['sys_idMember']);
			//Xet tat ca cac buoc tu buoc 1 den control_status=1 dau tien->muc dich tim user la nguoi tao co nam trong cac buoc day hay khong
			$sql_workflow_steps_t_all = "SELECT * FROM workflow_steps INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id WHERE workflow_valid_dates.wvd_wf_id ='".$valid_wf."' and workflow_steps.wfs_delete_status = 'n' and wvd_valid_date = '".$valid."' order by workflow_steps.wfs_id asc ";
			$query_t_all = mysql_query($sql_workflow_steps_t_all);
			$key_t_all=0;
			$i_t_all=0;
			while($row_t_all = mysql_fetch_array($query_t_all)){
				if($row_t_all['wfs_control_status']==1){
					$key_t_all++;
					$i_t_all=0;
					break;
				}
                    $array_user = explode(',',$row_t_all['wfs_user_id']);
					if(count(array_intersect($array_user,$sosanh_user_t_all))>0){
					   	$string_step_temp_all .= $row_t_all['wfs_step'].',';
					}
                    $sql_group_mem ='select gm_user_id from group_members where gm_g_id in('.$row_t_all['wfs_g_id'].') and gm_delete_status="n" ';
                    $query_member = mysql_query($sql_group_mem);
            		if(mysql_num_rows($query_member)>0){
            		    $g_user_id = array();
            			while($row_member_group = mysql_fetch_array($query_member)){
                                array_push($g_user_id,$row_member_group['gm_user_id']);                    
                        }                   
            			if(count(array_intersect($g_user_id,$sosanh_user_t_all))>0){
            			     $string_step_temp_all .= $row_t_all['wfs_step'].',';
            			            			 
            			}
            		}
		}		
        $sub_string_step_temp_all = substr($string_step_temp_all,0,-1);
        $max_step_string = explode(',',$sub_string_step_temp_all);
        $max_step = max($max_step_string);
        for($i=1;$i<=$max_step;$i++){
            $string_step_t_all .=$i.',';
        }
        $process_user_groupuser = false;
		if($string_step_t_all!=''){
			$process_user_groupuser = true;
		}
		//End xet
        //Kiem tra dung rang nguoi tao do co nam trong quy trinh duyet tu buoc 1 den buoc kiem soat dau tien
		if($process_user_groupuser){
			//Gia tri se duoc lay theo quy trinh
			$sql_steps_t_all = "SELECT * FROM workflow_steps INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id	WHERE workflow_valid_dates.wvd_wf_id ='".$valid_wf."' and workflow_steps.wfs_delete_status = 'n' and wvd_valid_date = '".$valid."' and wfs_step not in (".substr($string_step_t_all,0,-1).") order by workflow_steps.wfs_id asc";
			$sql_workflow_step_run_t_all = mysql_query($sql_steps_t_all);
			if(mysql_num_rows($sql_workflow_step_run_t_all)>0){
				//gia tri cua mang nay se duoc luu vao trong request_steps
				$sql_min_step_user_t_all = " SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id	FROM workflow_steps	INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id WHERE workflow_valid_dates.wvd_wf_id = '".$valid_wf."' AND workflow_steps.wfs_delete_status = 'n' AND wvd_valid_date = '".$valid."' AND wfs_step not in (".substr($string_step_t_all,0,-1).") GROUP BY workflow_valid_dates.wvd_wf_id ORDER BY workflow_steps.wfs_id ASC	";
				$run_sql_min_step_user_t_all = mysql_query($sql_min_step_user_t_all);
				$row_process_user_fetch_t_all = mysql_fetch_array($run_sql_min_step_user_t_all);
                //Lay lai user id hoac group user id dau tien de thuc hien viec gui mail va luu vao step dau tien
				$row_process_user_t_all = $row_process_user_fetch_t_all['wfs_user_id'];
				$row_process_group_t_all = $row_process_user_fetch_t_all['wfs_g_id'];
				//Xu ly insert vao bang requests					
				$sql_request_t_all = "INSERT INTO requests(r_module_id,r_form_id,r_status,r_wf_id,r_process,r_email_content,r_register_user,r_register_date,r_update_date) VALUES('".$module_id."','".$rfa_id."','".trim($approval)."','".$valid_m."','".$r_process."','".mysql_real_escape_string($mail_contents)."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','".$date_timestamp."')";
				$result_step_one = mysql_query($sql_request_t_all);
                if($result_step_one){
				    $step_request_id_one =  mysql_insert_id();
                }
                else{
                    echo "Your information input data have error";
                    echo "<br />";
                    echo '<input type="button" name="cancel" value="Back" onclick="history.go(-2);" />';
                    exit();
                }
				//Xu ly insert vao bang request_steps
				$i=0;
				while($row_workflow_steps_t_all = mysql_fetch_array($sql_workflow_step_run_t_all)){
				    $i++;
                    if($i==1){
                        $times = $date_timestamp;
                    }else{
                        $times = "0000-00-00 00:00:00";                      
                    }
					//Lay g_id de sao luu
					$wfs_g_id_step1 = $row_workflow_steps_t_all['wfs_g_id'];
					//Lay user_id de sao luu
					$wfs_user_id_step1 = $row_workflow_steps_t_all['wfs_user_id'];
                    $wfs_deadline = $row_workflow_steps_t_all['wfs_deadline'];
					//Thuc hien viec insert du lieu cho bang request_steps                                                                                                                                                                                                        
					$sql_request_steps_step1 = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_register_user,rs_register_date,rs_update_date) VALUES('".$step_request_id_one."','".$i."','".$wfs_g_id_step1."','".$wfs_user_id_step1."','".$times."','".$wfs_deadline."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
					$sql_query_insert = mysql_query($sql_request_steps_step1);
				}
				//Ket thuc
				//Gui mail cho group hoac user duyet o lan duyet dau tien
				send_mail_for_group_or_user($row_process_user_t_all,$row_process_group_t_all,$subject,$mail_contents,$mail_cc);
			}
			else{
			 //Thuc hien viec update du lieu se duoc approval neu khong ton tai cac dieu tren
				switch($module_id){
					case 6:
					update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                    sendMailFromSettingContractAll($action_send,$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
					break;
					case 8:
					update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,'',$more_inf,$mail_contents);
					break;
					case 11:
					update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
					break;
					case 14:
					update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
					//note $object_title=$_SESSION['service_code']
					break;
					case 15:
					update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
					break;
					
				}	
			}
		}
		else{
			//Gia tri khong lay theo quy trinh khi co buoc kiem soat
			$sql_steps_t_all =" SELECT *
			FROM workflow_steps
			INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
			WHERE workflow_valid_dates.wvd_wf_id = '".$valid_wf."'
			AND workflow_steps.wfs_delete_status = 'n'
			AND wvd_valid_date = '".$valid."'
			ORDER BY workflow_steps.wfs_id ASC";
			//gia tri cua mang nay se duoc luu vao trong request_steps
			$sql_workflow_step_run_t_all = mysql_query($sql_steps_t_all);
			if(mysql_num_rows($sql_workflow_step_run_t_all)>0){
				$sql_min_step_user_t_all = "
				SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id
				FROM workflow_steps
				INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
				WHERE workflow_valid_dates.wvd_wf_id = '".$valid_wf."'
				AND workflow_steps.wfs_delete_status = 'n'
				AND wvd_valid_date = '".$valid."'
				GROUP BY workflow_valid_dates.wvd_wf_id
				ORDER BY workflow_steps.wfs_id ASC 
				";
				$run_sql_min_step_user_t_all = mysql_query($sql_min_step_user_t_all);
				$row_process_user_fetch_t_all = mysql_fetch_array($run_sql_min_step_user_t_all);
                //Lay lai user id hoac group user id dau tien de thuc hien viec gui mail va luu vao step dau tien
				$row_process_user_t_all = $row_process_user_fetch_t_all['wfs_user_id'];
				$row_process_group_t_all = $row_process_user_fetch_t_all['wfs_g_id'];
				//Xu ly insert vao bang requests					
				$sql_request_t_all = "INSERT INTO requests(r_module_id,r_form_id,r_status,r_wf_id,r_process,r_email_content,r_register_user,r_register_date,r_update_date) VALUES('".$module_id."','".$rfa_id."','".trim($approval)."','".$valid_m."','".$r_process."','".mysql_real_escape_string($mail_contents)."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','".$date_timestamp."')";
				$result_step_two =  mysql_query($sql_request_t_all);
                if($result_step_two){               
				    $step_request_id_two =  mysql_insert_id();
                }
                else{
                    echo "Your information input data have error";
                    echo "<br />";
                    echo '<input type="button" name="cancel" value="Back" onclick="history.go(-2);" />';
                    exit();
                }
				//Xu ly insert vao bang request_steps
					$step_count_two =0;
				while($row_workflow_steps_t_all = mysql_fetch_array($sql_workflow_step_run_t_all)){
				    $step_count_two++;
                    if($step_count_two==1){
                        $times = $date_timestamp;
                    }else{
                        $times = "0000-00-00 00:00:00";                      
                    }
					//Lay g_id de sao luu
					$wfs_g_id_t_all = $row_workflow_steps_t_all['wfs_g_id'];
					//Lay user_id de sao luu
					$wfs_user_id_t_all = $row_workflow_steps_t_all['wfs_user_id'];
                    $wfs_deadline = $row_workflow_steps_t_all['wfs_deadline'];
					//Thuc hien viec insert du lieu cho bang request_steps
					$sql_request_steps_t_all = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_register_user,rs_register_date,rs_update_date) VALUES('".$step_request_id_two."','".$step_count_two."','".$wfs_g_id_t_all."','".$wfs_user_id_t_all."','".$times."','".$wfs_deadline."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
					$sql_query_insert = mysql_query($sql_request_steps_t_all);					
				}
				//Gui mail cho group hoac user duyet o lan duyet dau tien
				send_mail_for_group_or_user($row_process_user_t_all,$row_process_group_t_all,$subject,$mail_contents,$mail_cc);
			}
			else{
                //tien hanh viec update du lieu neu khong thoa man dieu kien tren
				switch($module_id){
					case 6:
					update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                    sendMailFromSettingContractAll($action_send,$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
					break;
					case 8:
					update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
					break;
					case 11:
					update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
					break;
					case 14:
					update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
					break;
					case 15:
					update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
					break;
					
				}
			}
		}
	//Ket thuc voi wf co buoc kiem soat	
	}
	else
	{
        //kiem tra xem user id cua nguoi tao co nam trong group member hay khong
        $sql_get_g_id = "SELECT gm_g_id FROM group_members WHERE gm_user_id =".$_COOKIE["sys_idMember"]." AND gm_delete_status = 'n' ";
        $query_get_g_id = mysql_query($sql_get_g_id);
        $string_get_g_id = '';
        if(mysql_num_rows($query_get_g_id)>0){   
            while($row_getg_id = mysql_fetch_array($query_get_g_id)){
                $string_get_g_id .=','.$row_getg_id['gm_g_id'];
            }
        }
        else{
            $string_get_g_id .='';
        }     
        //Lay step lon nhat ma id user cua nguoi tao nam trong group member
		$sql_get_step_max = "SELECT *
		FROM workflow_steps
		INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
		WHERE workflow_valid_dates.wvd_wf_id ='".$valid_wf."' and workflow_steps.wfs_delete_status = 'n' and wvd_valid_date = '".$valid."' and ( wfs_user_id = '".$_COOKIE["sys_idMember"]."' ";
        if($string_get_g_id!=""){
            $sql_get_step_max .=    " or wfs_g_id  in(".substr($string_get_g_id,1).") ";
        }
        else{
            $sql_get_step_max .="";
        }
        $sql_get_step_max .= ") order by workflow_steps.wfs_id desc limit 0,1 ";
        $query_check = mysql_query($sql_get_step_max);
        //kiem tra rang la co nguoi tao nam trong do
    if(mysql_num_rows($query_check)>0){
        $rows_check = mysql_fetch_array($query_check);
        $rows_check_step = $rows_check['wfs_step'];
		//gia tri cua mang nay se duoc luu vao trong request_steps
		$sql_steps_all = "SELECT *
		FROM workflow_steps
		INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
		WHERE workflow_valid_dates.wvd_wf_id ='".$valid_wf."' and workflow_steps.wfs_delete_status = 'n' and wvd_valid_date = '".$valid."' and wfs_step > ".$rows_check_step." order by workflow_steps.wfs_id asc";				
        $sql_workflow_step_run = mysql_query($sql_steps_all);
		if(mysql_num_rows($sql_workflow_step_run)>0){	
		//lay min steps de luu vao bang requests	
			$sql_min_step_user = "
			SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id 
			FROM workflow_steps
			INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
			WHERE workflow_valid_dates.wvd_wf_id = '".$valid_wf."'
			AND workflow_steps.wfs_delete_status = 'n'
			AND wvd_valid_date = '".$valid."'
			AND wfs_step > ".$rows_check_step."
			GROUP BY workflow_valid_dates.wvd_wf_id
			ORDER BY workflow_steps.wfs_id ASC 
			";
			$run_sql_min_step_user = mysql_query($sql_min_step_user);
			$row_process_user_fetch = mysql_fetch_array($run_sql_min_step_user);
            //lay lai id user hoac id group user de gui mail va luu vao buoc duyet dau tien
			$row_process_user = $row_process_user_fetch['wfs_user_id'];
			$row_process_group = $row_process_user_fetch['wfs_g_id'];
			//Xu ly insert vao bang requests				
			$sql_request = "INSERT INTO requests(r_module_id,r_form_id,r_status,r_wf_id,r_process,r_email_content,r_register_user,r_register_date,r_update_date) VALUES('".$module_id."','".$rfa_id."','".trim($approval)."','".$valid_m."','".$r_process."','".mysql_real_escape_string($mail_contents)."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','".$date_timestamp."')";
			$result_step_three = mysql_query($sql_request);
            if($result_step_three){
			 $step_request_id_three =  mysql_insert_id();
            }
            else{
                echo "Your information input data have error";
                echo "<br />";
                echo '<input type="button" name="cancel" value="Back" onclick="history.go(-2);" />';
                exit();
            }
			//Xu ly insert vao bang request_steps
				$step_count_three=0;
			while($row_workflow_steps = mysql_fetch_array($sql_workflow_step_run)){
			    $step_count_three++;
                if($step_count_three==1){
                        $times = $date_timestamp;
                    }else{
                        $times = "0000-00-00 00:00:00";                      
                    }
				//Lay g_id de sao luu
				$wfs_g_id = $row_workflow_steps['wfs_g_id'];
				//Lay user_id de sao luu
				$wfs_user_id = $row_workflow_steps['wfs_user_id'];
                $wfs_deadline = $row_workflow_steps['wfs_deadline'];
				//Thuc hien viec insert du lieu cho bang request_steps
				$sql_request_steps = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_register_user,rs_register_date,rs_update_date) VALUES('".$step_request_id_three."','".$step_count_three."','".$wfs_g_id."','".$wfs_user_id."','".$times."','".$wfs_deadline."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
				$sql_query_insert = mysql_query($sql_request_steps);
			}
		//Ket thuc
		//Gui mail cho group hoac user duyet o lan duyet dau tien
		send_mail_for_group_or_user($row_process_user,$row_process_group,$subject,$mail_contents,$mail_cc);
		}	
		else{
			switch($module_id){
				case 6:
				update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                sendMailFromSettingContractAll($action_send,$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
				break;
				case 8:
				update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
				break;
				case 11:
				update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
				break;
				case 14:
				update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
				break;
				case 15:
				update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
				break;
				
			}
		}
	}
	else{
		//Gia tri khong lay theo quy trinh khi khong co buoc kiem soat
		$sql_steps_t_all =" SELECT *
		FROM workflow_steps
		INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
		WHERE workflow_valid_dates.wvd_wf_id = '".$valid_wf."'
		AND workflow_steps.wfs_delete_status = 'n'
		AND wvd_valid_date = '".$valid."'
		ORDER BY workflow_steps.wfs_id ASC";
		//gia tri cua mang nay se duoc luu vao trong request_steps
		$sql_workflow_step_run_t_all = mysql_query($sql_steps_t_all);
		if(mysql_num_rows($sql_workflow_step_run_t_all)>0){
			$sql_min_step_user_t_all = "
			SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id
			FROM workflow_steps
			INNER JOIN workflow_valid_dates ON workflow_steps.wfs_wvd_id = workflow_valid_dates.wvd_id
			WHERE workflow_valid_dates.wvd_wf_id = '".$valid_wf."'
			AND workflow_steps.wfs_delete_status = 'n'
			AND wvd_valid_date = '".$valid."'
			GROUP BY workflow_valid_dates.wvd_wf_id
			ORDER BY workflow_steps.wfs_id ASC 
			";
			$run_sql_min_step_user_t_all = mysql_query($sql_min_step_user_t_all);
			$row_process_user_fetch_t_all = mysql_fetch_array($run_sql_min_step_user_t_all);
            //lay lai id user hoac id group user de gui mail va luu vao buoc duyet dau tien
			$row_process_user_t_all = $row_process_user_fetch_t_all['wfs_user_id'];
			$row_process_group_t_all = $row_process_user_fetch_t_all['wfs_g_id'];
			//Xu ly insert vao bang requests
			$sql_request_t_all = "INSERT INTO requests(r_module_id,r_form_id,r_status,r_wf_id,r_process,r_email_content,r_register_user,r_register_date,r_update_date) VALUES('".$module_id."','".$rfa_id."','".trim($approval)."','".$valid_m."','".$r_process."','".mysql_real_escape_string($mail_contents)."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','".$date_timestamp."')";
			$result_step_four =  mysql_query($sql_request_t_all);
            if($result_step_four){
                $step_request_id_four =  mysql_insert_id();
            }
            else{
                echo "Your information input data have error";
                echo "<br />";
                echo '<input type="button" name="cancel" value="Back" onclick="history.go(-2);" />';
                exit();
            }
			//Xu ly insert vao bang request_steps
					$step_count_four =0;
				while($row_workflow_steps_t_all = mysql_fetch_array($sql_workflow_step_run_t_all)){
				    $step_count_four++;
                    if($step_count_four==1){
                        $times = $date_timestamp;
                    }else{
                        $times = "0000-00-00 00:00:00";                     
                    }
						//Lay g_id de sao luu
						$wfs_g_id_t_all = $row_workflow_steps_t_all['wfs_g_id'];
						//Lay user_id de sao luu
						$wfs_user_id_t_all = $row_workflow_steps_t_all['wfs_user_id'];
                        $wfs_deadline = $row_workflow_steps_t_all['wfs_deadline'];
						//Thuc hien viec insert du lieu cho bang request_steps
						$sql_request_steps_t_all = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_register_user,rs_register_date,rs_update_date) VALUES('".$step_request_id_four."','".$step_count_four."','".$wfs_g_id_t_all."','".$wfs_user_id_t_all."','".$times."','".$wfs_deadline."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
						$sql_query_insert = mysql_query($sql_request_steps_t_all);
				}
			//Ket thuc
			//Gui mail cho group hoac user duyet o lan duyet dau tien
			send_mail_for_group_or_user($row_process_user_t_all,$row_process_group_t_all,$subject,$mail_contents,$mail_cc);
		}
		else{
		  //update lai du lieu khi cac dieu kien tren la khong thoa man
			switch($module_id){
				case 6:
				update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                sendMailFromSettingContractAll($action_send,$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
				break;
				case 8:
				update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
				break;
				case 11:
				update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
				break;
				case 14:
				update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
				break;
				case 15:
				update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
				break;
				
			}
		  }
        }
	   //Ket thuc xu ly quy trinh
      }
}

//End
#----------------------------#
//Update total RFA
//Begin
function rfa_update_total($workflow_id,$module_id,$rfa_id,$date_timestamp,$subject,$content,$mail_cc,$object_title,$more_inf,$app_status,$app_status_edit,$mail_contents)
{		
	/* note
		$workflow_id = chinh la wf validate da duoc tao
		$module_id = module_id cua rfa(contract:6,client:8,proposal:11,ope_opc:14, ope_opi:15)
		$rfa_id = Chinh la id cua(contract,client,proposal,ope_opc,ope_opi) duoc lay ve
		$date_timestamp, kieu du lieu truyen vao la date_time 0000-00-00 00:00:00
		$subject: Chu de mail;
		$content: Noi dung mail;
		- subject va content da duoc lay tu $rfa_id truoc day
		$mail_cc: chinh la mail cua nguoi tao ra cac rfa
	*/
   	   //update status of object rfa
       if($module_id==6){
        //xu ly lai wf cho contract
        $sql_valid = " SELECT wvd_id, wvd_valid_date as valid,wvd_wf_id	FROM workflow_valid_dates WHERE wvd_wf_id = '".$workflow_id."'	AND now() >= wvd_valid_date	AND wvd_delete_status = 'n'	ORDER BY wvd_valid_date DESC
	   LIMIT 0 , 1 ";
	   $run_valid = mysql_query($sql_valid);
	   $row_valid =  mysql_fetch_array($run_valid);
    	   if(mysql_num_rows($run_valid)>0){
    		$workflow_id = $row_valid['wvd_id'];
    	   }
       }
   	   	if($app_status_edit!="approval"){
			echo '1<br/>';
	   	   	switch($module_id){
				case 6:
					$sql_update_rfa = " UPDATE contract set app_status_edit='waiting' where contract_id='".$rfa_id."' and delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 8:
					$sql_update_rfa = " UPDATE client_list set app_status_edit='waiting' where Client_id='".$rfa_id."' and delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 11:
					$sql_update_rfa = " UPDATE quotation_for_service set app_status_edit='waiting' where qfs_id='".$rfa_id."' and qfs_delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 14:
					$sql_update_rfa = " UPDATE opc_contracts set app_status='waiting' where opc_id='".$rfa_id."' and opc_delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 15:
					
					$sql_update_rfa = " UPDATE opi_internal_tasks set app_status='waiting' where opi_id='".$rfa_id."' and opi_delete_status='n'";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
			}
 		}
 		else{
			echo '2<br/>';
			switch($module_id){
				case 6:
					$sql_update_rfa = " UPDATE contract set app_status='waiting' where contract_id='".$rfa_id."' and delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 8:
					$sql_update_rfa = " UPDATE client_list set app_status='waiting' where Client_id='".$rfa_id."' and delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 11:
					$sql_update_rfa = " UPDATE quotation_for_service set app_status='waiting' where qfs_id='".$rfa_id."' and qfs_delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 14:
					$sql_update_rfa = " UPDATE opc_contracts set app_status='waiting' where opc_id='".$rfa_id."' and opc_delete_status='n' ";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
				case 15:
					echo '2<br/>';
					$sql_update_rfa = " UPDATE opi_internal_tasks set app_status='waiting' where opi_id='".$rfa_id."' and opi_delete_status='n'";
					mysql_query($sql_update_rfa);
					print mysql_error();
					break;
			}
		}
   	   if($app_status_edit!="approval"){
			echo '3<br/>';
    	   $sql_update_request = "UPDATE requests set r_update_date='".$date_timestamp."',r_status='waiting',r_email_content='".$mail_contents."' where r_form_id='".$rfa_id."' and r_module_id='".$module_id."' and r_process = 'Edit' and r_status <> 'approval' and r_status <> 'waiting' and r_wf_id =".$workflow_id;
    		mysql_query($sql_update_request);
    		print mysql_error();
    		//Get r_id from request
    	$r_ids = "select r_id from requests where r_delete_status='n' and r_form_id='".$rfa_id."' and r_module_id='".$module_id."' and r_process = 'Edit' and r_status = 'waiting' and r_wf_id =".$workflow_id;
     
    		$run_r_ids = mysql_query($r_ids);
    		$row_r_ids = mysql_fetch_array($run_r_ids);
    		$sql_request_id = $row_r_ids['r_id'];
            
        }
        else{
			echo '4<br/>';
            $sql_update_request = "UPDATE requests set r_update_date='".$date_timestamp."',r_status='waiting',r_email_content='".$mail_contents."' where r_form_id='".$rfa_id."' and r_module_id='".$module_id."' and r_process = 'Create' and r_wf_id =".$workflow_id;
    		mysql_query($sql_update_request);
    		print mysql_error();
    		//Get r_id from request
			$r_ids = "select r_id from requests where r_delete_status='n' and r_form_id='".$rfa_id."' and r_module_id='".$module_id."' and r_process = 'Create' and r_wf_id =".$workflow_id;
    		$run_r_ids = mysql_query($r_ids);
    		$row_r_ids = mysql_fetch_array($run_r_ids);
    		$sql_request_id = $row_r_ids['r_id'];
            
        }
		//update rs_version
		$version = "select max(rs_version)  as version from request_steps where rs_r_id=".$sql_request_id;
		$qr_version = mysql_query($version);
		$row_version = mysql_fetch_array($qr_version);
		$num_version = $row_version['version']+1;
		$sql_all = "SELECT *
		FROM workflow_steps where wfs_wvd_id = '".$workflow_id."' and wfs_delete_status = 'n' HAVING wfs_control_status = 1 order by workflow_steps.wfs_id asc ";
		$run_mysql_all = mysql_query($sql_all);
        //Kiem tra quy trinh voi buoc kiem soat
        $control_status = false;
		if(mysql_num_rows($run_mysql_all)>0){
			$control_status = true;
		}
    //Neu ton tai buoc kiem soat(control_status)
	if($control_status){
		echo '5<br/>';
		$string_step_temp_all = '';
        $string_step_t_all = '';
        $sosanh_user_t_all = array($_COOKIE['sys_idMember']);
    		//Xet tat ca cac buoc tu buoc 1 den contron_status=1 dau tien
			$sql_workflow_steps_t_all = "SELECT * FROM workflow_steps WHERE wfs_wvd_id ='".$workflow_id."' and wfs_delete_status = 'n' order by workflow_steps.wfs_id asc";
			
			$query_t_all = mysql_query($sql_workflow_steps_t_all);
			$key_t_all=0;
			$i_t_all=0;
			while($row_t_all = mysql_fetch_array($query_t_all)){
				if($row_t_all['wfs_control_status']==1){
				$key_t_all++;
				$i_t_all=0;
				//continue;
				break;
				}
				$array_user = explode(',',$row_t_all['wfs_user_id']);
					if(count(array_intersect($array_user,$sosanh_user_t_all))>0){
					   	$string_step_temp_all .= $row_t_all['wfs_step'].',';
					}
                    $sql_group_mem ='select gm_user_id from group_members where gm_g_id in('.$row_t_all['wfs_g_id'].') and gm_delete_status="n" ';
                    $query_member = mysql_query($sql_group_mem);
            		if(mysql_num_rows($query_member)>0){
            		    $g_user_id = array();
            			while($row_member_group = mysql_fetch_array($query_member)){
                                array_push($g_user_id,$row_member_group['gm_user_id']);                    
                        }                   
            			if(count(array_intersect($g_user_id,$sosanh_user_t_all))>0){
            			     $string_step_temp_all .= $row_t_all['wfs_step'].',';
            			            			 
            			}
            		}
		}		
        $sub_string_step_temp_all = substr($string_step_temp_all,0,-1);
        $max_step_string = explode(',',$sub_string_step_temp_all);
        $max_step = max($max_step_string);
        for($i=1;$i<=$max_step;$i++){
            $string_step_t_all .=$i.',';
        }
        $process_user_groupuser = false;
		if($string_step_t_all!=''){
			$process_user_groupuser = true;
		}
		//Neu user do co ton tai
		if($process_user_groupuser){
			//Gia tri se duoc lay theo quy trinh
			$sql_steps_t_all = "SELECT *
			FROM workflow_steps
			WHERE wfs_wvd_id ='".$workflow_id."' and wfs_delete_status = 'n' and wfs_step not in (".substr($string_step_t_all,0,-1).") order by workflow_steps.wfs_id asc";
			$sql_workflow_step_run_t_all = mysql_query($sql_steps_t_all);
			if(mysql_num_rows($sql_workflow_step_run_t_all)>0){
				//gia tri cua mang nay se duoc luu vao trong request_steps
				$sql_min_step_user_t_all = "
				SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id
				FROM workflow_steps
				WHERE wfs_wvd_id = '".$workflow_id."'
				AND wfs_delete_status = 'n'
				AND wfs_step NOT IN (".substr($string_step_t_all,0,-1).")
				GROUP BY wfs_wvd_id
				ORDER BY wfs_id ASC 
				";
				$run_sql_min_step_user_t_all = mysql_query($sql_min_step_user_t_all);
				$row_process_user_fetch_t_all = mysql_fetch_array($run_sql_min_step_user_t_all);
				$row_process_user_t_all = $row_process_user_fetch_t_all['wfs_user_id'];
				$row_process_group_t_all = $row_process_user_fetch_t_all['wfs_g_id'];
				//Xu ly insert(update lai) vao bang request_steps
				$step_count_update=0;
				while($row_workflow_steps_t_all = mysql_fetch_array($sql_workflow_step_run_t_all)){
				    $step_count_update++;
                    if($step_count_update==1){
                        $time_new = $date_timestamp;
                    }else{
                        $time_new = "0000-00-00 00:00:00";
                    }
        				//Lay g_id de sao luu
        				$wfs_g_id_t_all = $row_workflow_steps_t_all['wfs_g_id'];
        				//Lay user_id de sao luu
        				$wfs_user_id_t_all = $row_workflow_steps_t_all['wfs_user_id'];
                        $wfs_deadline = $row_workflow_steps_t_all['wfs_deadline'];
        				//Thuc hien viec insert du lieu cho bang request_steps
        				$sql_request_steps = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_version,rs_register_user,rs_register_date,rs_update_date) VALUES('".$sql_request_id."','".$step_count_update."','".$wfs_g_id_t_all."','".$wfs_user_id_t_all."','".$time_new."','".$wfs_deadline."','".$num_version."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
        				$sql_query_insert = mysql_query($sql_request_steps);
				}
			     //Gui mail cho group hoac user duyet o lan duyet dau tien
				send_mail_for_group_or_user($row_process_user_t_all,$row_process_group_t_all,$subject,$mail_contents,$mail_cc);
			}
			else{				
				switch($module_id){
						case 6:
						update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                        sendMailFromSettingContractAll('3',$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
						break;
						case 8:
						update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
						break;
						case 11:
						update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
						break;
						case 14:
						update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
						break;
						case 15:
						update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
						break;
						
					}
				
			}
		}
		else{
			//Gia tri khong lay theo quy trinh
			$sql_steps_t_all =" SELECT *
			FROM workflow_steps
			WHERE wfs_wvd_id= '".$workflow_id."'
			AND workflow_steps.wfs_delete_status = 'n'
			ORDER BY workflow_steps.wfs_id ASC";
			//gia tri cua mang nay se duoc luu vao trong request_steps
			$sql_workflow_step_run_t_all = mysql_query($sql_steps_t_all);
			if(mysql_num_rows($sql_workflow_step_run_t_all)>0){
				$sql_min_step_user_t_all = "
				SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id
				FROM workflow_steps
				WHERE wfs_wvd_id = '".$workflow_id."'
				AND wfs_delete_status = 'n'
				GROUP BY wfs_wvd_id
				ORDER BY wfs_id ASC 
				";
				$run_sql_min_step_user_t_all = mysql_query($sql_min_step_user_t_all);
				$row_process_user_fetch_t_all = mysql_fetch_array($run_sql_min_step_user_t_all);
				$row_process_user_t_all = $row_process_user_fetch_t_all['wfs_user_id'];
				$row_process_group_t_all = $row_process_user_fetch_t_all['wfs_g_id'];
				//Xu ly insert vao bang request_steps
				$step_count_update_two =0;
				while($row_workflow_steps_t_all = mysql_fetch_array($sql_workflow_step_run_t_all)){
					$step_count_update_two++;
                    if($step_count_update_two==1){
                        $time_new = $date_timestamp;
                    }else{
                        $time_new = "0000-00-00 00:00:00";
                    }
                        //Lay g_id de sao luu
    					$wfs_g_id_t_all = $row_workflow_steps_t_all['wfs_g_id'];
    					//Lay user_id de sao luu
    					$wfs_user_id_t_all = $row_workflow_steps_t_all['wfs_user_id'];
                        $wfs_deadline = $row_workflow_steps_t_all['wfs_deadline'];
    					//Thuc hien viec insert du lieu cho bang request_steps
    					$sql_request_steps = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_version,rs_register_user,rs_register_date,rs_update_date) VALUES('".$sql_request_id."','".$step_count_update_two."','".$wfs_g_id_t_all."','".$wfs_user_id_t_all."','".$time_new."','".$wfs_deadline."','".$num_version."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
    				    $sql_query_insert = mysql_query($sql_request_steps);
				}
                //Gui mail cho group hoac user duyet o lan duyet dau tien
				send_mail_for_group_or_user($row_process_user_t_all,$row_process_group_t_all,$subject,$mail_contents,$mail_cc);
			}
			else{
				switch($module_id){
					case 6:
					update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                    sendMailFromSettingContractAll('3',$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
					break;
					case 8:
					update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
					break;
					case 11:
					update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
					break;
					case 14:
					update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
					break;
					case 15:
					update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
					break;
					
				}
			}
		}
	}
	else{ 
		echo '6<br/>';
                //Xet xem nguoi tao do co nam trong bang group_members hay khong
                $sql_get_g_id = "SELECT gm_g_id FROM group_members WHERE gm_user_id =".$_COOKIE["sys_idMember"]." AND gm_delete_status = 'n' ";
                $query_get_g_id = mysql_query($sql_get_g_id);
                $string_get_g_id = '';
                if(mysql_num_rows($query_get_g_id)>0){   
                    while($row_getg_id = mysql_fetch_array($query_get_g_id))
                    {
                        $string_get_g_id .=','.$row_getg_id['gm_g_id'];
                    }
                    
                }
                else{
                    $string_get_g_id .='';
                }   
                //Neu co se ghi du lieu ra mot string;
                //Lay lai ban ghi dau tien(max) ma co chua user la nguoi tao ra quy trinh duyet
        		$sql_get_step_max = "SELECT * FROM workflow_steps WHERE wfs_wvd_id ='".$workflow_id."' and wfs_delete_status = 'n' and ( wfs_user_id = '".$_COOKIE["sys_idMember"]."' ";
                if($string_get_g_id!=""){
                    $sql_get_step_max .=    " or wfs_g_id  in(".substr($string_get_g_id,1).") ";
                }
                else{
                    $sql_get_step_max .="";
                }
                $sql_get_step_max .= ") order by workflow_steps.wfs_id desc limit 0,1 ";
                $query_check = mysql_query($sql_get_step_max);
                //Neu ban ghi dau tien ton tai
				if(mysql_num_rows($query_check)>0){
                $rows_check = mysql_fetch_array($query_check);
                $rows_check_step = $rows_check['wfs_step'];
				//gia tri cua mang nay se duoc luu vao trong request_steps
				$sql_steps_all = "SELECT *
				FROM workflow_steps
				WHERE wfs_wvd_id ='".$workflow_id."' and wfs_delete_status = 'n' and wfs_step > ".$rows_check_step." order by wfs_id asc";				
				$sql_workflow_step_run = mysql_query($sql_steps_all);
				if(mysql_num_rows($sql_workflow_step_run)>0){	
				//lay min steps de luu vao bang requests	
					$sql_min_step_user = "
					SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id 
					FROM workflow_steps
					WHERE wfs_wvd_id = '".$workflow_id."'
					AND  wfs_delete_status = 'n'
					AND wfs_step > ".$rows_check_step."
					GROUP BY wfs_wvd_id
					ORDER BY wfs_id ASC 
					";
					$run_sql_min_step_user = mysql_query($sql_min_step_user);
					$row_process_user_fetch = mysql_fetch_array($run_sql_min_step_user);
					$row_process_user = $row_process_user_fetch['wfs_user_id'];
					$row_process_group = $row_process_user_fetch['wfs_g_id'];
					//Xu ly insert vao bang request_steps
					$step_count_update_three =0;
					while($row_workflow_steps = mysql_fetch_array($sql_workflow_step_run)){
					   $step_count_update_three++;
                       if($step_count_update_three==1){
                            $time_new = $date_timestamp;
                        }else{
                            $time_new = "0000-00-00 00:00:00";
                        }
    						//Lay g_id de sao luu
    						$wfs_g_id = $row_workflow_steps['wfs_g_id'];
    						//Lay user_id de sao luu
    						$wfs_user_id = $row_workflow_steps['wfs_user_id'];
                            $wfs_deadline = $row_workflow_steps['wfs_deadline'];
    						//Thuc hien viec insert du lieu cho bang request_steps
    						$sql_request_steps = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_version,rs_register_user,rs_register_date,rs_update_date) VALUES('".$sql_request_id."','".$step_count_update_three."','".$wfs_g_id."','".$wfs_user_id."','".$time_new."','".$wfs_deadline."','".$num_version."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
    						$sql_query_insert = mysql_query($sql_request_steps);
					}
                //Gui mail cho group hoac user duyet o lan duyet dau tien
				send_mail_for_group_or_user($row_process_user,$row_process_group,$subject,$mail_contents,$mail_cc);
				}	
				else{
					switch($module_id){
							case 6:
							update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                            sendMailFromSettingContractAll('3',$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
							break;
							case 8:
							update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
							break;
							case 11:
							update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
							break;
							case 14:
							update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
							break;
							case 15:
							update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
							break;
							
						}
				}
			}
			else{
				echo '8<br/>';
				//Gia tri khong lay theo quy trinh
				$sql_steps_t_all =" SELECT *
				FROM workflow_steps
				WHERE wfs_wvd_id = '".$workflow_id."'
				AND workflow_steps.wfs_delete_status = 'n'
				ORDER BY workflow_steps.wfs_id ASC";
				//gia tri cua mang nay se duoc luu vao trong request_steps
				$sql_workflow_step_run_t_all = mysql_query($sql_steps_t_all);
				if(mysql_num_rows($sql_workflow_step_run_t_all)>0){
					echo '8.1<br/>';
					$sql_min_step_user_t_all = "
					SELECT MIN(wfs_step) ,wfs_user_id,wfs_g_id
					FROM workflow_steps
					WHERE wfs_wvd_id = '".$workflow_id."'
					AND wfs_delete_status = 'n'
					GROUP BY wfs_wvd_id
					ORDER BY wfs_id ASC 
					";
					$run_sql_min_step_user_t_all = mysql_query($sql_min_step_user_t_all);
					$row_process_user_fetch_t_all = mysql_fetch_array($run_sql_min_step_user_t_all);
					$row_process_user_t_all = $row_process_user_fetch_t_all['wfs_user_id'];
					$row_process_group_t_all = $row_process_user_fetch_t_all['wfs_g_id'];
					echo '8.3<br/>';
					//Xu ly insert vao bang request_steps
						$step_count_update_four=0;
						while($row_workflow_steps_t_all = mysql_fetch_array($sql_workflow_step_run_t_all)){
							$step_count_update_four++;
                            if($step_count_update_four==1){
                                $time_new = $date_timestamp;
                            }else{
                                $time_new = "0000-00-00 00:00:00";
                            }
    							//Lay g_id de sao luu
    							$wfs_g_id_t_all = $row_workflow_steps_t_all['wfs_g_id'];
    							//Lay user_id de sao luu
    							$wfs_user_id_t_all = $row_workflow_steps_t_all['wfs_user_id'];
    							$wfs_deadline = $row_workflow_steps_t_all['wfs_deadline'];
    							//Thuc hien viec insert du lieu cho bang request_steps
    							$sql_request_steps = "INSERT INTO request_steps(rs_r_id,rs_step,rs_g_id,rs_user_id,rs_start,rs_duration,rs_version,rs_register_user,rs_register_date,rs_update_date) VALUES('".$sql_request_id."','".$step_count_update_four."','".$wfs_g_id_t_all."','".$wfs_user_id_t_all."','".$time_new."','".$wfs_deadline."','".$num_version."','".$_COOKIE["sys_idMember"]."','".$date_timestamp."','0000-00-00 00:00:00')";
    						    $sql_query_insert = mysql_query($sql_request_steps);
						}
                    //Gui mail cho group hoac user duyet o lan duyet dau tien
					send_mail_for_group_or_user($row_process_user_t_all,$row_process_group_t_all,$subject,$mail_contents,$mail_cc);
				}
				else{
					echo '8.2<br/>';
					switch($module_id){
							case 6:
							update_approval_rfa_contract('contract','contract_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
                            sendMailFromSettingContractAll('3',$rfa_id,"CONTRACT::".($object_title)."::".$more_inf,$mail_contents);
							break;
							case 8:
							update_approval_rfa_client('client_list','Client_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
							break;
							case 11:
							update_approval_rfa_proposal('quotation_for_service','qfs_id',$rfa_id,'',$mail_cc,$object_title,$more_inf,$mail_contents);
							break;
							case 14:
							update_approval_rfa_opc('opc_contracts','opc_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
							break;
							case 15:
							update_approval_rfa_opi('opi_internal_tasks','opi_id',$rfa_id,'',$mail_cc,$object_title,$mail_contents);
							break;
							
						}
				}
			}
	}
	//Ket thuc xu ly quy trinh	
}
//End
############################################ End ##############################################
function new_client_in_period($client_id)
{
	$sql_ok1 = "SELECT client_id
	FROM `contract`
	WHERE con_id = '0'
	AND client_id_2 = '0'
	AND delete_status = 'n'
	AND app_status='approval'";
	$sql_ok1 .=" GROUP BY client_id
	UNION
	SELECT client_id
	FROM `contract`
	WHERE con_id = '0'
	AND client_id_2 <> '0'
	AND app_status='approval'
	AND delete_status = 'n'
	";
	$sql_ok1 .= " GROUP BY client_id
	ORDER BY client_id ASC
	";
	$run1 = mysql_query($sql_ok1);
	$array1 = array();
	if(mysql_num_rows($run1)>0)
	{
		while($row1=mysql_fetch_array($run1))
		{
			array_push($array1,$row1['client_id']);
		}
	}
	else
	{
		array_push($array1,0);	
	}
	$str1 = "0,".implode(',', $array1).",0";
		
	$sql_all = "SELECT DISTINCT Client_id FROM client_list
				LEFT JOIN quotation_for_service ON client_list.Client_id = quotation_for_service.qfs_client_id
				WHERE Client_id
				IN ( ";
	$sql_all .= $str1;
	$sql_all .= " ) ";
	$sql_all .=" AND client_list.app_status = 'approval'
				 AND client_list.delete_status = 'n'
				 AND client_list.Client_id='".$client_id."'";
	$sql_query = mysql_query($sql_all);
	if(mysql_num_rows($sql_query)>0)
	{
		return 'No';
	}
	else
	{
		return 'Yes';
	}
}
function strip_only($str, $tags, $stripContent = false) {
    $content = '';
    if(!is_array($tags)) {
        $tags = (strpos($str, '>') !== false ? explode('>', str_replace('<', '', $tags)) : array($tags));
        if(end($tags) == '') array_pop($tags);
    }
    foreach($tags as $tag) {
        if ($stripContent)
             $content = '(.+</'.$tag.'[^>]*>|)';
         $str = preg_replace('#</?'.$tag.'[^>]*>'.$content.'#is', '', $str);
    }
    return $str;
}
#####################################
//Permistion Contract Setting
function permistionSettingContract($id_user,$cf_name){
    //$id_user= $_COOKIE["sys_idMember"];
    //Lay lai id group tu user truyen vao
    $sql_group = "SELECT gm_g_id FROM group_members WHERE gm_user_id='".$id_user."' and gm_delete_status='n'";
    $qr_group = mysql_query($sql_group);
    if(mysql_num_rows($qr_group)>0){
        $string_group = '0';
        while($row_group = mysql_fetch_array($qr_group)){
            $string_group .= ','.$row_group['gm_g_id'];
        }
        $group_id = substr($string_group,2);
    }else{
        $group_id="";
    }
    //lay id user or id group tu permistion_contract
    $sql_per = "SELECT MAX(pc_permission) as pc_permission FROM permission_contract WHERE pc_delete_status = 'n' "; 
    if($group_id!=""){
        $sql_per .=" AND (pc_user_id ='".$id_user."' OR pc_group_id in(".$group_id.") ) ";
    }else{
        $sql_per .=" AND pc_user_id='".$id_user."' ";
    }
    $qr_per = mysql_query($sql_per);
    if(mysql_num_rows($qr_per)>0){
        $row_per = mysql_fetch_array($qr_per);
        $permistion = $row_per['pc_permission'];
    }else{
        $permistion =0;
    }
    $array_permistion_read = array("Browse_contract","Search_contract","View_contract","View_finish_contract","View_project","View_my_contract","Export_information_contract","Export_time_budget","Export_debit_note","Export_detailed_time_budget");
    $array_permistion_contribute = $array_permistion_read;
    array_push($array_permistion_contribute, "Create_contract", "Edit_contract", "Delete_contract", "Debit_note_control", "Close_project");
    $array_permistion_design = $array_permistion_contribute;
    array_push($array_permistion_design,"Apporove");
    $array_permistion_full = $array_permistion_design;
    array_push($array_permistion_full,"Debit_note_without_control");
    switch($permistion){
        case 1:
        if(in_array($cf_name,$array_permistion_read)){
            return true;
        }else{
            return false;
        }
        break;
        case 2:
        if(in_array($cf_name,$array_permistion_contribute)){
            return true;
        }else{
            return false;
        }
        break;
        case 3:
        if(in_array($cf_name,$array_permistion_design)){
            return true;
        }else{
            return false;
        }
        break;
        case 4:
        if(in_array($cf_name,$array_permistion_full)){
            return true;
        }else{
            return false;
        }
        break;
        case 0:
        return false;
        break;
    }
}
#####################################
//repacle special when send mail
function pregReplace($string){
$partern = array('/&amp;/');
$repl_part = array('&');
return str_replace('\\','',preg_replace($partern,$repl_part,$string));
}
?>